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

AaronBertrand — Sun, 19 Jul 2009 15:26:00 GMT

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:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/07/a-little-management-studio-oops.aspx

http://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://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://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.

Search results matching tags 'Security', 'management studio', and 'database list'

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