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.
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.
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.
Then Right Click on the server, and choose Properties.
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!
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:
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:
You could even access DatabaseB by typing “use DatabaseB”.
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.
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.
Written by Montreal DBA Team