2

I have a MSSQL 2005 server set up and running. I would like to be able to have the following setup:

database_one with user_one as db_owner
and
database_two with user_two as db_owner

The tricky part (for me at least) is that I would like database_one to be hidden from user_two and database_two to be hidden from user_one.

So far, I've found the VIEW ANY DATABASE permission. Playing around with this it seems like no matter how I set up the more specific permissions for a user, the VIEW ANY DATABASE permission takes precedence.

If I run DENY VIEW ANY DATABASE TO user_one, connecting as user_one will show only the master and tempdb system databases, even if user_one is set up as db_owner of database_one.

Is there a way to set up the permissions to do what I want?

6
  • Maybe I'm misunderstanding, but what is gained by not allowing user_one to even see database_two? As long as user_one doesn't have any permissions allowing access to the database, does it hurt that he can see that it exists?
    – Adrien
    Jan 15, 2010 at 20:41
  • It may hurt, it may not. If the two users are clients, they may want to remain under the illusion that they are the only users on the box (even if they know otherwise).
    – Johrn
    Jan 15, 2010 at 21:05
  • Hmm. serverfault ate my comment. You have a valid concern. I'd say your only solution is Chopper3's suggestion of instances, although that won't be foolproof (think SQL Server Browser service).
    – Adrien
    Jan 15, 2010 at 21:17
  • Well shoot. I'm going to leave this open for a bit longer, but I'll be back to accept Chopper3's answer if nothing else turns up.
    – Johrn
    Jan 15, 2010 at 21:34
  • How do you suppose it is that they'll be seeing the databases? Will they have access to any SQL management tools?
    – joeqwerty
    Jan 16, 2010 at 0:40

2 Answers 2

0

The visibility of sys.databases:

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.

So the default visibility is exactly what you're asking for: each user is able to see master, tempdb and it's own database. Make sure that user_one is the owner of database_one (not merely a member of the db_owner role!), as in:

ALTER AUTHORIZATION ON DATABASE::database_one TO user_one;

If the setup is correct (user_one is owner of database_one, user_two is owner of database_two) and no extra permsisionsa re granted then your should be getting exactly what you're asking for. Specifically, your shouldn't have to DENY anything, the fact that you have to DENY a permission implies somethings wrong with your test (you granted extra, unneeded, permissions to the logins of user_one and user_two).

1
  • Thank you! It looks like what happened was that VIEW ANY DATABASE was being granted by default, AND I was incorrectly setting up the user as a member of db_owner instead of properly as owner. So when I would DENY VIEW ANY DATABASE, the user's db would no longer be visible.
    – Johrn
    Jan 19, 2010 at 17:21
1

Would separate instances help?

1
  • That would probably work, but it seems like a way heavier solution than I am looking for.
    – Johrn
    Jan 15, 2010 at 21:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .