July 19, 2009 | SQL Server

For shared SQL Server providers : hiding your list of databases from customers

In a shared SQL Server hosting environment, there are several problems that can arise when you let your customers use Management Studio to connect and administer their databases.  In the typical case, you give them a single SQL Authentication username and password, and they are supposed to be able to connect only to their database.  By default, however, Object Explorer and Object Explorer Details will gladly present the entire list of databases on the server.  In this case, not only are you exposing all of your customers' database names to each other, and letting your customers know exactly how many databases are sharing the server, but you also introduce the chance for customer support calls.  This is because of at least two factors:

(a) enumerating all of the databases on the server can take a long time, and could potentially time out;
(b) checking each database can result in errors if the database is set to AutoClose.

While (a) seems to have been fixed in SQL Server 2008 CU3 (see http://support.microsoft.com/kb/958760/), (b) still seems to be a big issue for some customers.  For a lot more background information, see the following:

/blogs/aaron_bertrand/archive/2008/07/07/a-little-management-studio-oops.aspx

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=355238

There are some workarounds posted there that may help end users avoid the problem, but last night fellow MVP Erland Sommarskog reminded me that there is another possible solution from the provider end:

DENY VIEW ANY DATABASE TO [login];

This may not be appropriate in all situations, but in the hosted case where you have a mapping of one login to one database, it may be okay.  You can try this out in an isolated case using the following code:

USE [master];
GO
CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
CREATE LOGIN [foo] WITH
    PASSWORD = N'foo',
    DEFAULT_DATABASE = [foo],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO
CREATE LOGIN [bar] WITH
    PASSWORD = N'bar',
    DEFAULT_DATABASE = [bar],
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO
USE [foo];
GO
CREATE USER [foo]
    FOR LOGIN [foo]
    WITH DEFAULT_SCHEMA = [dbo];
GO
USE [bar];
GO
CREATE USER [bar]
    FOR LOGIN [bar]
    WITH DEFAULT_SCHEMA = [dbo];
GO
USE [master];
GO
DENY VIEW ANY DATABASE TO [foo];
DENY VIEW ANY DATABASE TO [bar];
GO

The good news: if you connect as foo, you will not be able to see the bar database, and if you connect as bar, you will not be able to see the foo database.  The bad news: you also won't be able to see your own database in Object Explorer, because the DENY VIEW setting prevents the check that would bypass the restriction for the databases you own.  The screen shot below demonstrates that you login foo can see the databases foo, master and tempdb in sys.databases and in the query editor's database drop-down.  However, foo cannot see the foo database in Object Explorer.

This is certainly a bug and was reported way back:

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=124651

Since the database will not show up in Object Explorer, there are many things the end user won't be allowed to do (such as right-clicking the database and viewing properties, expanding lists of objects, etc.), but this *will* allow them to run queries against their own database from a query window (as long as their default database is not set to a database where they don't have permissions).  So, this could be a solution that is valuable for the hosted scenario, where you want to minimize the chance for errors, or where you simply don't want your customers to know how many databases are on your server or who those customers are.  Just be sure that your customers understand that they won't be able to use Object Explorer until this bug is fixed.

As a few people have noted, you could get around this by making the login the owner of the database.  This may not always be possible in the hosted scenario, but once they brought it up I thought it was important to mention.  If you have created the above database as scripted, you can test this change as follows:

USE foo;
GO
DROP USER foo;
GO
USE bar;
GO
DROP USER bar;
GO
USE [master];
GO
ALTER AUTHORIZATION ON DATABASE::foo TO [foo];
ALTER AUTHORIZATION ON DATABASE::bar TO [bar];
GO

Now when you connect via Object Explorer, as expected, you will be able to see *your* database(s) in the list.

Erland's suggestion is to add VIEW DEFINITION permissions per database, allowing you to customize database access at a higher level.  This is certainly a better way to do it, and they are hopefully going to look at it for the next version of SQL Server:

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=273830

Finally, Steve Kass made a casual suggestion to name the databases differently: instead of naming my database "aaronbertrand.com" they could use a GUID for the name (and I would simply change my connection strings to point to the GUID as the database name).  While this doesn't hide the number of databases on the server, and arguably makes technical support and troubleshooting at least one step more complicated, it can certainly obscure customers' names from each other.  (As long as you don't expose the mapping of database name <-> GUID anywhere that your customers can get to, since support will need to perform this translation quite often.)  I would suggest using NEWID() and not NEWSEQUENTIALID() if you are going to go this route, because my database name of [C08CD…] is going to be easier to spot in a list or drop-down if there aren't 400 other similarly-named databases on the server.  And you will want to eliminate the dashes and skip any NEWID() values that are generated with a leading digit, as these will be problematic as identifiers.

File Attachment: Picture%2080.zip

11 comments on this post

    • Davide Mauri - July 19, 2009, 9:51 PM

      Just a little addition to your post: if the "foo" user is dbo of its own database he CAN see the SSMS's object explorer, and then manage the DB.

    • AaronBertrand - July 19, 2009, 11:40 PM

      Thanks Davide, I've updated the content.

    • Ben Nevarez - July 20, 2009, 1:43 AM

      Unfortunately there are several other issues with this approach. For example, some applications and tools like ODBC Data Source Administrator, use the list of databases from sysdatabases (can be seen in Profiler), so legal users will not be able to see the list of the databases they have access to while configuring an ODBC connection.
      On the other side, some functions like DB_NAME() are not aware of this metadata security and they still show the name of any database, even if you do not have access to it.
      Ben

    • Erik Bitemo - November 11, 2009, 11:47 AM

      I'm fighting with the same problem on SQL 2000. The weirdest thing to me is that if you connect via SQL 2000 Query Analyzer, it works as expected… Added +1 vote to Erland's suggestion.

    • Abdul Wadood - May 24, 2010, 3:57 PM

      How i can view all the default databases of SQL Server 2005?

    • AaronBertrand - May 24, 2010, 4:05 PM

      SELECT name, default_database_name
      FROM sys.server_principals;

    • Abdul Wadood - May 24, 2010, 4:41 PM

      Thanks for comments AaronBertrand but i need databases just like master,model,msdb and tempdb.There is also more databases like that i want this..If there is answer please help me. I new in SQl server.

    • Gerardo Cavallini - October 17, 2011, 10:13 PM

      Need to know how I can deny the possibility of a backup to prevent logins to write to a directory server.

    • Sanket Kokane - October 4, 2012, 1:12 PM

      Great.Save my day

    • Raphael - July 24, 2013, 9:51 PM

      Use SQL 2012 and use contained Database
      http://www.codeproject.com/Articles/526621/Top-20-exciting-features-of-SQL-Server-2012-Part-1#Feature_number_4_(Revolution):-_Contained_database
      Great feature, love it…

    • laxman - August 23, 2013, 11:25 AM

      hi anybody tell me how to make table with password protected in sql server 2008 please suggest me as soon as possible.
      plz email me nnnlaxman12@gmail.com

Comments are closed.