Search This Blog

Tuesday, 4 December 2012

SQL Server - How to display only one Database to an account

For the purpose of a Hosted Service I needed to display a SQL database to a third party, I did not want to expose any databases except those for their client so I needed to find an approach.

SQL Server Databases


Here you can see that I have several databases on the SQL Server, the database I need to present to the third party is called Troy_Live

SQL Server Logins


I have created a new SQL Server Login for the third party support to access the SQL Server Management Studio.

Access to SQL Server


I will now change the access rights to the SQL Server for my support account, so on the root of the SQL Server right click and choose Properties.

Deny Access to View Any Database


Select the pemissions option and then highlight your Security Login. You can now choose the permissions for this account and to hide all the databases from this account you need to choose the View any Database and set this to Deny

Set Permissions on Database you want to access


Now we have denied permisison to view all databases we need to allow acces to work on the correct database. Choose the properties of the database you want and then on the files option set the owner as the account you want to have access.

Logon as Support User


Now when I logon to the SQL Server Management Studio as my support user I can see only the database for my client and the master and tempdb databases.

No comments:

Post a Comment