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.
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
CREATE DATABASE MyPrivateDatabase
--Now, create the test users account, inside their database
CREATE LOGIN myUser with password = 'me', check_policy = off
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!
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.
With this default configuration our user can see their database, MyPrivateDatabase, and my reporting services databases. Attempts to access one of these databases will result in the following error.
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 visibility 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 denies the user permission to view databases.
--Make the user the DBO
--Switch to master DB
--Revoke View permission
DENY VIEW ANY DATABASE TO Myuser
With these steps completed if we login as myUser we will see the following in object explorer which is exactly the intended effect.
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.