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?

  • 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". Jan 23, 2012 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, 2012 at 19:21

2 Answers 2


"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

  • 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, 2012 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, 2012 at 21:04

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


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.

  • 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, 2012 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. Jan 23, 2012 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, 2012 at 20:45
  • @jl01 can you please describe your comment.thanks
    – DooDoo
    Jan 24, 2012 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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