Wednesday, April 15, 2009

Mapping SQL user to a single database, hiding all others

It's straightforward to setup a user to have access to a single database, but the default problem is that although they may not be able to access the other databases, the user can still see the full list of databases on the server.

In order to get around this we need to do the following:

1. Ensure the user is setup but is not mapped to the database yet.

2. Remove the ability to view all databases for this user (or role)

In Management Studio, right click the server then click "Properties".  Click on "Permissions" and then select the particular user and select "Deny" on "View Any Database".

3. Change the owner of the database to this user

USE db_name
GO
EXEC sp_changedbowner 'user_name'
GO

No comments: