Tell me more ×
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.
up vote 0 down vote favorite

I have a large database in Sql Server 2008 that has many Tables , Stored Procedure, Functions.this database has several users.what can I do for Users that each user after connect to Managment Studio just see his/her related objects BUT can execute other functions,Stored procedures and Select other tables.

thanks alot

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 – Matin Jan 23 '12 at 19:21

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. – Matin 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
up vote 2 down vote

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? – Matin 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 – Matin Jan 24 '12 at 10:28

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.