October 15, 2008

Limit SQL Server Database List

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.



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

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!

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 databases 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 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
sp_changedbowner 'myUser'

--Switch to master DB

--Revoke View permission

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


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.

tags: Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.