August 17, 2012

How to allow a SQL Login to see only ONE database


How to allow a SQL Login to see only ONE database

On one server, there are a lot of other databases. Sometimes the databases are created as per client name and so when we log with one client login credentials, he can see which are other clients I am working with.
We do not want them to be able to see all the other databases on the instance.  They have access to only one database and that is the only one that they should see in object explorer.

To implement this, we can use following steps.

e.g. we want to allow client1_login to  see only client1_db database.

USE [master]
GO
-- make sure they can view all databases for the moment.
GRANT VIEW ANY DATABASE TO client1_login
GO

USE client1_db
go

-- drop the user in the database if it already exists.
IF  EXISTS (SELECT *
            FROM sys.database_principals
            WHERE name = N'client1_login ')
  DROP USER client1_login
GO

-- grant them ownership to of the database (ownership of dbo schema).
ALTER AUTHORIZATION ON DATABASE::client1_db  to client1_login
go

USE MASTER
go

-- deny ability to see ohter databases
DENY VIEW ANY DATABASE TO client1_login
go

No comments: