Hiding User Databases | SansSQL

Thursday, March 21, 2013

Hiding User Databases

In my previous post "Hide System databases and objects", I had explained on how to hide the system databases and system objects. In this post I will explain how to hide "User Databases".
Hiding User Databases may not be necessary all the times, but in some cases when your SQL Server instance hosts mission critical databases to which the business puts a rule saying no one else apart from them should be able to see their database. (off course admins are exception)
For Example consider an SQL Server instance that hosts database for HR and Finance. In this case a HR user should not be able to see Finance Database and a Finance user should not be able to see HR database.

To simulate and test this,
I have created 2 databases named
  1. HR
  2. Finance
I have created 5 Users
  1. HRAdmin -- Owner of HR Databases and Default DB is "HR"
  2. HRReadWrite -- This user has read and write access on HR databases and Default DB is "HR"
  3. FinanceAdmin -- Owner of Finance Database and Default DB is "Finance"
  4. FinanceReadWrite -- This user has read and write access on Finance databases and Default DB is "Finance"
  5. ReadWriteBoth -- This user has read and write access on both databases and Default DB is "master"
Below is the view for an Administrator
Now, let us Deny "View Any Databases" permissions for the "public" user
use [master]
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO
After Denying permission to public user, the view for an administrator is still the same.

Now let us connect as the user "HRAdmin", and below is the view for HRAdmin
HRAdmin is able to see only the "HR" user database because it is the owner of this database alone.

Next, connect as "HRReadWrite". HRReadWrite user is not able to see any user databases in Object explorer, but can do read and write operations on "HR" Database alone.

Now let us connect as "FinanceAdmin", since FinanceAdmin is owner of Finance database, this user is able to see only "Finance" user Databaase.


Now connect as "FinanceReadWrite". FinanceReadWite user is not able to see any user databases in Object explorer, but can do read and write operations on "Finance" user Database alone.


Now let us connect using the user "ReadWriteBoth" which has read and write access on both the databases HR and Finance. This user is unable to see any user database in object explorer, but can do read and write operations on both HR and Finance database using the query editor.


No comments:

Post a Comment