Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 –  Kerezo Jan 23 '12 at 19:21
add comment

2 Answers

up vote 3 down vote accepted

"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

share|improve this answer
    
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. –  Kerezo 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
add comment

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.

share|improve this answer
    
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? –  Kerezo 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 –  Kerezo Jan 24 '12 at 10:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.