Database.ca SQL Server Blog

My Blog

Monday, October 07 2013

Keywords: SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Create a SQL Login that does not see all databases on the server

A question I get every now and then is how to create a SQL Server login who can only view database(s) it has been assigned to, but not the list of all databases. Clients could understandably want this for privacy or security reasons, or both. SQL Server handles this case in a half-baked way. It does allow you to deny view on all databases, but at the same time it is too restrict and makes it look like that it is also denying you permissions on your own databases as well. We’ll see what it does with an example. For the purposes of demonstration I have created 3 databases on my SQL Server 2012 server: DatabaseA, DatabaseB & DatabaseC.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Then I created a login using SQL Server Authentication, called ClientAB. This client mainly uses DatabaseA and sometimes DatabaseB. It is important to set the default database for this client to DatabaseA.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

We give this login db_datareader, db_datawriter and db_ddladmin rights on DatabaseA and DatabaseB. You could also give it the db_owner permission if you wish.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Then Right Click on the server, and choose Properties.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Click on the Permission tab on the left. Select ClientAB login from the list of logins on top right hand side, and then scroll down until “View any database” and click on Deny. We’re now done!

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Let’s now test the whole thing. The first test will be from SQL Server Management Studio. We’ll do this by first connecting to the database using ClientAB’s credentials:

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

No databases can be seen in the Object Explorer! But wait! Don’t get discouraged! Everything else actually works well. Click on “New Query” button on the top, and a new window opens up, with its database successfully connected to (and showing up) DatabaseA. And what’s more? We can also query DatabaseB by using a 3 part object name:

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

You could even access DatabaseB by typing “use DatabaseB”.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

The final test will be by creating ODBC…and once again it works well. The list of other databases is not displayed on the drop down box of database names.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

And although, DatabaseB did Not show up on the list…you could type it in and the system will not only accept the value, but the tests will be successful as well.

 

SQL Server, User, Login, Deny View any Database, Privacy, Security, Permissions

Written by Ramin Haghighat