0

I have a large SQL Server 2008 database that has many tables, stored procedures, and functions. This database also has several users.

How can I make it so that each user, after connecting to the database via Management Studio, can only see his/her related objects in the object browser BUT can execute functions, stored procedures and select tables owned by other users?

Nick Chammas
  • 14,670
  • 17
  • 75
  • 121
DooDoo
  • 203
  • 1
  • 3
  • 12
  • Can you elaborate on "his/her objects" and "other functions, stored procedures . . . ". Everything in a database is an object, so you need to be clear about your use of the word "objects". – OliverAsmus Jan 23 '12 at 19:17
  • For Example john has this objects:(tblOrders,sp_GetOrders,func_GetState) and Sara has this objects(tblUsers,GetUsers,Func_GetState_User) I want when John login to SSMS just see his object but can execute or select Sara's objects – DooDoo Jan 23 '12 at 19:21

2 Answers2

3

Sounds like you need to employ some database schemas to separate out user objects:

http://msdn.microsoft.com/en-us/library/ms190387.aspx

A database schema is nothing more than a collection of objects within a database. DBO and SYS are common built-in schemas in SQL Server. You can create a schema that houses a user's objects (tables, functions, stored procs . . .) and assign a level of security to that schema (owner, reader . . . ). These help especially in terms of object-level administration.

OliverAsmus
  • 878
  • 7
  • 11
  • thanks,but how I can set security settings between Schemas?for example it I want John just see his objects but don't see Sara's Objects but can select or execute those objects? – DooDoo Jan 23 '12 at 19:32
  • Within the database, click on Security ~ Schemas ~ then double click the schema you want to add security to. The second of the 3 tabs that list for the schema says "Permissions". This is where you can assign permissions. – OliverAsmus Jan 23 '12 at 19:46
  • I must be missing something but John should be able to see the objects he has created. GRANT VIEW on SarasObject to Domain\John is what I would recommend if John needed to view the object Sara had created. You can also do this in SSMS. – jl01 Jan 23 '12 at 20:45
  • @jl01 can you please describe your comment.thanks – DooDoo Jan 24 '12 at 10:28
3

"Metadata visibility" determines what objects a user can see. Basically, their own objects (login, users) or what they have permissions on (tables, code etc).

You can't hide an object that they have select/execute premission on. Simple.

What you can do if to use schemas to create object groupings in SSMS to "declutter" John and Sarah's view. However, this is a poor use of schema in my opinion

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
  • thank you very much.nice to see you in dba.StackExchange.In general I can't do this,yes?I think Sql Server has weakness in this matter. – DooDoo Jan 24 '12 at 20:45
  • I'd say it makes sense. If they have permissions, they can see it. No permissions mean they can't – gbn Jan 24 '12 at 21:04