How to hide SQL Server user databases in SQL Server Management Studio

By:   |   Comments (26)   |   Related: 1 | 2 | > SQL Server Management Studio Configuration


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.

Conneting to users for which DENY view is enabled

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.

Change DB Owner of 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.

Output of hiding database after changing database owner

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.

View for a newly created login with db_owner on both databases

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, August 13, 2018 - 3:15:22 PM - Gary Back To Top (77166)

Sorry but I'm confused why this is labelled SSMS since this is altering backend security which affects any access tool not just SSMS. 

I thought by the title it was some trick in SSMS which in fact there is - its called Filter Settings in SSMS. 

Please consider that making any user an owner of a database is an incredibly bad idea.  What happens when that person leaves your company? 


Thursday, May 17, 2018 - 12:24:26 PM - David Back To Top (75969)

Thanks Manvendra:

I tried to browse all the comments, but I did not see another option:

instead of deny run 

REVOKE VIEW ANY DATABASE TO PUBLIC

and then GRANT VIEW ANY DATABASE TO the groups or IDs that you want the databases and have the users add a SSMS filter so they only see their DBs.

Not quite what you asked for, but functionally similar (and we like to remove all grants from public).

 

Thanks,
David

 


Wednesday, January 31, 2018 - 1:37:07 PM - Vasanth Back To Top (75070)

Hi Manvendra,

It seems to be like user has to be the owner of perticular DB which the user is only intended to see that perticular DB. We have a requirement like suppose 'Test' is a DB on 'Myisnatance' DB server(Instance) and there are many users like A, B, C so on and we cant grant all of them DB_owner permission and all of them are suppose to see only 'Test' database and should be able to Read/Write. There are many databases created on 'Myisnatance' DB server(Instance) and users should be able to see their perticular DB's like 'Test', not being owners and should be able to Read/Write. Could you please help in this scenario.

Many thanks in advance!

Vasanth


Wednesday, October 11, 2017 - 6:13:11 PM - KRev Back To Top (67219)

Thanks M.

Is there any way to do this for a group,because when i tried we are getting below error on SQL 2016.

Msg 15353, Level 16, State 1, Line 4

An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.

 


Wednesday, July 15, 2015 - 6:52:32 AM - Piyush Kumar Back To Top (38211)

Thanks Manvendra, its very helpful to hide all database from other sql user.


Wednesday, July 1, 2015 - 8:28:35 AM - Rahul Back To Top (38094)

Hi,

Its not working for me.

I tried command DENY VIEW ANY DATABASE TO USER_A; and User_A is db_owner of TestDB.

Use_A is not able to view any database even he is owner of TestDB.

Tested on SQL server 2008.


Thanks,

Rahul


Wednesday, December 17, 2014 - 12:30:28 AM - SIBU SAMAL Back To Top (35631)

Thanks Manvendra, its very helpful to hide all database from other sql user.


Wednesday, November 19, 2014 - 7:24:31 AM - Subhrangshu Banerjee Back To Top (35334)

I (sa) have created a database in SQL Server 2008 and it shows in server object explorer to all users that can login to SQL.

I want to hide it from those users, I want it to be viewable by me (sa) only.

DENY VIEW ANY DATABASE TO PUBLIC  ---  is hiding all db. I need to hide my db only to all othar users.
Plz suggest.


Wednesday, October 22, 2014 - 7:48:02 PM - Sadan Kumar B Back To Top (35039)

Thank you for the tip.

Worked perfect..


Thursday, September 18, 2014 - 1:42:18 PM - Sam Back To Top (34609)

Just to be clear, the user must be the database owner, not a member of the db_owner role.  There seems to be no practical way to hide databases without micromanaging things.


Sunday, July 6, 2014 - 8:58:31 PM - Rodrigo Back To Top (32565)

Hi,

I tried your solution nut it didn't work as expected... I don't know why.

As soon as I don't grant view any database permission to public (or I deny it on the target user)

that user won't see any database even if he's a db_owner of some of them.

I'm running SQL Server 2012.

Any idea?

Thanks.

 

-Rodrigo-

 


Wednesday, July 2, 2014 - 11:07:02 PM - Pankaj Giri Back To Top (32524)

can you provide me the complete scripts of pubs database for sql server 2012 managent studio


Wednesday, June 11, 2014 - 11:36:42 AM - Eric Zierdt Back To Top (32195)

This is an interesting solution; my first thought when I read the problem I thought, why not just filter the database list in the object explorer, like you can do wit the table list...but after looking at the object explorer, I see they didn't include a filter option at the database level.  Congrats on your 25th tip!


Sunday, June 8, 2014 - 6:54:29 AM - k. singh Back To Top (32141)

Hello,

Is there a way to resync a mirror at my DR site without a full backup after breaking the mirrors for a few hours. It takes a long time to get a full backup and trn log to the DR site. 


Wednesday, September 25, 2013 - 5:18:06 PM - Raj Back To Top (26951)

Thanks. This was a very useful information.

 

My question is same as asked by Dipak.

I wonder too if there  is way we can show database in SSMS while not making the user a db_owner or sysadmin and yet not giving view permissions on all databases.

I have this requirement and any tips would be helpful.

 


Saturday, September 21, 2013 - 9:51:45 AM - Nagaraju Back To Top (26884)

Thanks, Very useful


Wednesday, September 18, 2013 - 6:10:19 AM - Azim Back To Top (26840)

A note regarding the last para.

 

Denying a user not see all database in instance at the cost of master db ownership is not a wise idea. Logins that only see user databases with public role is not that much dangerous instead of giving a chance to a login to jeopardize an instance master db and bring everything down.

-Azim


Wednesday, September 11, 2013 - 2:34:56 PM - Gabriel Mafra Back To Top (26745)

Hello! great post, i just have one question:

 

I have a SQL 2012 Cluster enviroment and i set the permisssions to see databases exactly as you described, everything was perfect.

My nightmare started when another user required access to see database "A", how can i do that? the dbo schema of the database can be owned by only one user, so to give him the permissions he wanted i had to undo everything and all users were able see all databases.

Would you have any advice for my scenario??

 

Thanks in advance!


Wednesday, August 28, 2013 - 11:12:19 AM - Kevin Back To Top (26509)

I was reading the article "How to hide SQL Server user databases in SQL Server Management Studio" with great interest. However, while the command will prevent SQLCMD and OSQL access, and limited SSMS viewing, it does not prevent an account from actually viewing data. Executing "USE databasename SELECT * FROM tablename" will still allow that public account to view the contents of the table. 


Wednesday, August 28, 2013 - 9:33:09 AM - Jeff Moden Back To Top (26505)

Very nice tip.  I've not had the need to do such a thing as what you have in this article but I enjoyed the article and the very appropriate use/placement of graphics and code.  Well done, Mr. Singh.


Wednesday, August 28, 2013 - 5:01:39 AM - dipak Back To Top (26501)

This is a good post and useful to know.

One question related to your Conclusion above:

I have multiple users accessing their own databases, e.g. x users connecting to database A, y users connecting to database B.

Surely there must be a way to grant privileges for more than one user to view a particular database in SSMS?!

i.e. I want to "deny view any database to public", then enable MULTIPLE users to view their database in SSMS. but then avoid granting sysadmin or database owner to users (for obvious reasons!)


Saturday, August 17, 2013 - 1:19:39 AM - Manvendra Back To Top (26362)

Greg/Jeremy- Thank you very much!!!

 

Basu- Yes you can use "ÜSE DATABASE".


Wednesday, August 14, 2013 - 3:14:17 PM - Sumon Basu Back To Top (26309)

Hi Manavendra,

From your post we can see except db_owner none will be able to see the databases, until they have the sysadmin access priviledges. My question is whether Use <DatabaseName> will work with any query, if the user has the db_datareader access permission granted, I am asking this as I still not tested the same.

Regards,

Sumon


Wednesday, August 14, 2013 - 9:45:17 AM - Jeremy Kadlec Back To Top (26302)

Manvendra,

Congrats on your 25th tip!

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, August 14, 2013 - 8:40:41 AM - Greg Robidoux Back To Top (26299)

Manvendra, congrats on your 25th published tip.  Keep up the good work.

Greg


Wednesday, August 14, 2013 - 1:24:27 AM - Varun Gulati Back To Top (26292)

Good one for securing database names from user with no access to DB















get free sql tips
agree to terms