How database users see their objects in sql server

Question

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?

• OliverAsmus: 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".
• DooDoo: 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

Answer 1

"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

• DooDoo: 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.
• gbn: I'd say it makes sense. If they have permissions, they can see it. No permissions mean they can't

Answer 2

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.

• DooDoo: 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?
• OliverAsmus: 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.
• jl01: 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.