How to prevent users seeing all databases in sql server
To prevent users to see other databases you need to:
- run this
DENY VIEW ANY DATABASE TO public
- Remove the user from the db_owner role on the users database. (otherwise you cant set the user as owner on the database)
(user -> properies -> user mapping -> database role membership)
- Set the user as database owner on the database (current can be "sa" or the login which are used to connect to the db). Then will the user also be attached to the db_owner role, same role which was removed in in step 1.
(database -> properties -> files -> owner)
Now are the user both owner of the database and has the db_owner role.
Now will the user only see his own database + master and temp when listing databases in management studio. This needs to be done on all users and databases and on all new users created which will give a headache. Its not enough to have the db_owner role, the user needs to be the owner on the database to get it to work.
These settings should be set when a new DB user and database are created.
Note: JN I tried this 20130215 on guru, it worked
Leave a Comment