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.
-- make sure they can view all databases for the moment.
GRANT VIEW ANY DATABASE TO client1_login
-- drop the user in the database if it already exists.
IF EXISTS (SELECT *
WHERE name = N'client1_login ')
DROP USER client1_login
-- grant them ownership to of the database (ownership of dbo schema).
ALTER AUTHORIZATION ON DATABASE::client1_db to client1_login
-- deny ability to see ohter databases
DENY VIEW ANY DATABASE TO client1_login