Sql-server – Hiding all databases in SSMS that a user does not have access to without making them db_owner

permissionssql server

I am trying to make it so a test user can only see the dbs that he has access to. Currently, the user can only see the one db that he has access to, but he is now the db_owner of that database. I don't feel that a test user should be the db_owner as this gives him access to everything in the database. From the articles that I found online, this seems to be the only way to make it so the user can only see the single db. When I use DENY VIEW ANY DATABASE TO [user] he does not see any db within the server, including the db I have given explicit view privileges to. Is there a way to allow him to only view the single db without giving him db_owner privileges?

The article I am referencing is here.

Best Answer

First of all if you DENY VIEW ANY DATABASE TO [user] it will not "see" even the database where it's a memeber of db_owner role, except for sysadmin, only database owner will "see" the database owned by it.

Database owner is different from just membership in db_owner role, it's a login that has authorization on that database (grant authorization on database::myDB to myLogin), so only one login at a time can own the database, and the corresponding user has all the permissions in that database and can "see" it in OE even when VIEW ANY DATABASE is denied to the corresponding login.

Saying that there is an alternative solution to your problem, it's SQL Server 2012 Contained Database Feature

Starting with SQL Server 20012 you can enable Contained Database Feature at the instance level and then make your databases contained.

This means that there will be a database, not server to authenticate your users, and all these users will "see" only the databases where they were created. They will not "see" nothing at the server level.

If one day you'll need to migrate your databases to another server, you'll not need to transfer server logins to another server, your contained database already contains all its users and they are not "orphaned" because they don't have corresponding logins at all.

Related Question