Recently when working with a client I was asked how in a SQL Server database environment with multiple databases how we can prevent users from seeing all other databases on the server. Permissions are configured to restrict who can actually query the database, but in the end, individuals still can see the names of the other databases on the server, getting potentially an idea of who else you are hosting, or information that you just don't want to share. I have found many blog articles that talk about how to get around this, but all of them were a bit choppy, so I thought I would take a moment to walk through a demo of the process. This article walks through the entire scenario.

The Setup

To start we are working on a basic installation of SQL Server 2005 that has been configured to allow SQL Server connections. Default databases for ReportingServices are installed but nothing else. To start things off we will via T-SQL Create two databases and then a "myUser" SQL Server account. The following is the script needed to complete this.

 

CREATE DATABASE UsersDatabase
GO

CREATE DATABASE MyPrivateDatabase
GO

--Now, create the test users account, inside their database
USE UsersDatabase
GO

CREATE LOGIN myUser with password = 'me', check_policy = off
GO

 

This is very basic SQL, and used simply for illustration purposes, if you have databases configured already and users configured, you can use them as your examples!

The Issue

What is wrong with this basic setup? Well the issue is that if this user logs into the server, object explorer shows the following detail.

Default management display

With this default configuration our user can see their database, MyPrivateDatabase, and my reporting services databases. Attempts to access one of these databses will result in the following error.

Error Message

Rather than having this, we want to simply remove view permissions for everything else, that is what the next steps will do.

Removing View Access

The process of removing access is quite simple, however, there is ONE specific side-effect that you MUST be aware of. To limit the users visibilty you must make the users account the owner of the database. Now typically this isn't a big deal, but be sure to consider it PRIOR to implementing the following.

With our SQL Server connection still open and connected to the UsersDatabase run the following block of scripts. The first script will change the database owner, the second one changes the context back to the Master database, and the last one denys the user permission to view databases.

 

--Make the user the DBO
sp_changedbowner 'myUser'
GO

--Switch to master DB
USE MASTER
GO

--Revoke View permission
DENY VIEW ANY DATABASE TO Myuser
GO

 

With these steps completed if we login as myUser we will see the following in object explorer which is exactly the intended effect.

Final view

Conclusion

Depending on your specific scenarios this can be a very helpful way to limit a users view into your database system, and personally I find it as a must have when granting clients access to a shared SQL Server environment. Please be sure to share comments/feedback below, if you have specific help needs, please use the forum.