Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Help : How can I hide databases from users?

Posted by blakhani on June 25, 2011


This is one of the common questions I have seen on forums, most common request from administrators who have been given task to create multi-tenant SQL Server. This essentially means various department of company or various client of the hosting company would access same SQL Server Instance. Business owners demand that they want clients to see only those databases which are relevant for them. I have worked with a company where the database name was name of the client. Inability to hide name of databases would essentially mean exposing information about their other clients.

In simple words, if Contoso is a database hosting company who has “Northwind traders” and “TailSpin Toys” as their client. If TailSpin logs in to the SQL Server, they should not see “Northwind” database and vice versa.

SQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database. The sp_MSdbuseraccess stored procedure then runs against every database in SQL Server to determine the user’s permission level in that database. So this can be achieved by altering the stored procedure as documented in KB http://support.microsoft.com/?id=889696 (You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server)

In SQL 2005 onwards we fire queries to get the information in SQL Server Management Studio (a.k.a. SSMS) . Now, SQL Server 2005 onwards, we also have limited metadata visibility which I am going to use as “trick” to achieve our goal. As per http://msdn.microsoft.com/en-us/library/ms178534.aspx
{

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
}

So, here is the quick script which can be used to demo this. If you love SSMS then I have also given instruction.

  • Create Two databases.
Create Database [NorthWindDB]
go
Create Database [TailSpinDB]
go
  • Create Logins (Not mapped to any Database)
CREATE LOGIN [NorthWindLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
CREATE LOGIN [TailSpinLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
  • Right Click on the Root section of the SSMS Tree (SQL Server Name)>Properties>Permissions>Click on the account, and select Deny to view databases. OR use below T-SQL
REVOKE VIEW ANY DATABASE TO [NorthWindLogin]
go
REVOKE VIEW ANY DATABASE TO [TailSpinLogin]
go
  • Right Click on the newly created databases > Properties > Files, and change the Owner to the newly created accounts. OR use below T-SQL
use [NorthWindDB]
go
sp_changedbowner 'NorthWindLogin'
go
use [TailSpinDB]
go
sp_changedbowner 'TailSpinLogin'
go

Here is the final out come…

SSMS

NorthWindLogin can see only NorthWindDB database and TailSpinLogin can see only his database.

Erland Sommarskog, SQL MVP, has filed request “Need VIEW DEFINITION permissions per database”. If you feel this is useful then please vote here!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Advertisements

2 Responses to “Help : How can I hide databases from users?”

  1. Hello Balmukund,

    Excellent article and very helpful for DBA like me who have just started.
    i have question regarding this article suppose u want give access to the two users of northwind and tailsping with same condition that two from one company should not be able see the other database.
    I am asking since i guess u can give permission one person at a time in file—->owner.

    Please right some more articles since you choose live problem this is very helpful.

    Thanks,
    swapnil

  2. MAAV said

    Thank You very Much!! Very helpful for me! @avilavalenz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: