iHwy Development Blog

Here's a short script that allows a Microsoft SQL Server administrator to restrict the databases that a SQL Server user will see when they access the database server using SQL Server Management Studio. You'd think that this info was easy to find, but trust us, it's not. It's out there, but usually requires weeding through a lot of irrelevant info to get to the main points:

1. First, using Object Explorer in SQL Management Studio, expand the Security > Logins node of the tree. Then right click and select "New Login..."

2. Enter a login name and some SQL Server authentication info (password) for the user. Leave the "Default database" at "master". Do NOT select any user database in this step. Do NOT go to the User Mapping page in the Login creation dialog to assign the user to any db's, either. The user you are creating will not be the user on any specific databases.

3. Now run this for each db you want your new user to be able to see when they login to SQL Management Studio:

use [someDB] -- use the real db name
go
sp_changedbowner 'theUser' -- use the new username
go

4. Next, deny the new user "public" access to ALL databases:

use master
go
deny VIEW any DATABASE to theUser
go

That pretty much does it. You should now be able to login to SQL Management Studio as that user and only see the databases that they own.

Think of it this way: by making them the owner, you're specifying what databases they can see in SQL Management Studio. Don't mix this up with setting an existing database user as db_owner. It's not the same thing.