Problem
I have a SQL Server instance that has hundreds of databases. Navigating the database tree in SSMS is a pain and I was wondering if there was a way to limit the list of databases that I see in SSMS?
Solution
SQL Server consolidation is becoming more popular these days to reduce costs and therefore more and more databases are being put on one instance. It is very common to host multiple databases on a consolidated instance from multiple applications and departments and sometimes application owners want to hide their databases to other users of the instance. They do not want to make their database visible to others. This tip will give you an understanding on how databases can be hidden.
Setup
Suppose there are two databases A and B from two different applications and they are hosted on the same SQL Server instance. The users of database A are not allowed to see database B and vice versa. Here we will create two different logins user_A and user_B and give them appropriate rights to their own databases.
CREATE DATABASE A
GO
CREATE DATABASE B
GO
CREATE LOGIN user_A with password=’U$er_A@1234′
Go
CREATE LOGIN user_B with password=’U$er_B@1234′
Go
USE A
GO
CREATE USER user_A for login user_A;
GO
EXEC sp_addrolemember ‘db_owner’, ‘user_A’
GO
USE B
GO
CREATE USER user_B for login user_B
GO
EXEC sp_addrolemember ‘db_owner’, ‘user_B’
NOTE:-DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS
Hiding all user databases for all logins
Suppose you want to hide all databases for all logins. Generally we hide our databases for security purposes. We can run the below statements to hide all databases for all logins. The databases will then only be visible to sysadmin logins or owners of the database.
USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO
Once you run the above statement, you will not be able to see any databases in SQL Server Management Studio unless you are a sysadmin or your login is the owner of a database(s).
Here you can see in the below screen shot, I have connected using logins user_A and user_B and none of the user databases are showing after running the Deny View access to public.
Only sysadmins and database owners can see databases
To allow the logins to see their databases, I will make both logins the owners for their respective databases. User_A will be owner of database A and user_B will be the owner of database B. Run the below statements to change the database owners.
USE A
GO
SP_changedbowner [USER_A]
GO
USE B
GO
SP_changedbowner [USER_B]
We can check the database owners by running sp_helpdb. As you can see in the below screenshot that the database owners have been changed for both databases.
Now we can connect to the SQL Server instance again using both logins and see the changes compared to before. Here we can see that only one database is visible for both logins. Database A is visible to user_A and database B is visible to user_B. This is because both logins are now the database owners of these databases.
Does making a user a db_owner work
Now we will create a new login user_C and assign db_owner access to both databases and check whether these databases are visible to this new login.
CREATE LOGIN user_C with password=’U$er_c@13′
GO
USE A
GO
CREATE USER user_C for login user_C;
GO
EXEC sp_addrolemember ‘db_owner’, ‘user_C’
GO
USE B
GO
CREATE USER user_c for login user_C
GO
EXEC sp_addrolemember ‘db_owner’, ‘user_C’
As we can see below, neither of these databases are visible for login user_C. So from this we can see that you have to be the database owner to be able to see the databases in SQL Server Management Studio if the DENY VIEW ANY DATABASE is enabled for public.
Steps to hide databases for a specific login
Suppose we don’t want to do this across the board, but only do this for a specific login. We can run the below statement instead of DENY VIEW ANY DATABASE TO PUBLIC. After running the below statement, this login won’t be able to see databases except for any database that this login is the database owner, but all other logins can see the database as long as you did not also deny view to Public.
USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC; — turn this back on if it was off
GO
DENY VIEW ANY DATABASE TO USER_A;
GO
Steps to view all databases
By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server can see all databases in the instance. To grant the VIEW ANY DATABASE permission to a specific login or to all logins run the following query:
–To grant the VIEW ANY DATABASE permission to a specific login.
USE MASTER
GO
GRANT VIEW ANY DATABASE TO [login_name];
GO
–To grant the VIEW ANY DATABASE permission to public.
USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC;
Go
Note that if you use the DENY VIEW to PUBLIC this overrides the setting for an individual login, so if you DENY VIEW to PUBLIC and GRANT VIEW to a specific login this login will still not be able to see the databases.
If you are using DENY VIEW to PUBLIC and you want a login to still be able to see all databases without making that login a sysadmin you can do the following. Make the login a user in the master database and make that user a db_owner of the master database. This is not a very good option from a security perspective, but this does work. This way a login can see all databases without having to be a sysadmin.
Conclusion
As you can see from the above, there are limited options to hiding databases. Once you hide all databases the only logins that can see the databases are the logins that are the owners of the database or if the login is a sysadmin. Also, each database can only have one owner, so you can’t assign multiple owners to the same database.
Next Steps
- Follow this process to hide your databases in SQL Server Management Studio.
- Read more SSMS tips
