Allow SQL Server Login access to One or More databases

When you have Multiple or More than 2 SQL Server server.You were asked to create a new Login for a client who wishes to have access to their database. But you don't want to give them access to the other databases.

Please follow below Step:

Step 1:         Create new user

Step 2:         Deny view to any database

Step 3:         Authorized the user for that specific database , you have to use the  master by doing use master as below:

Please execute below query on Master DATABASE:

--Step 1: (create a new user)

create LOGIN MyUser WITH PASSWORD='MyUser@123', CHECK_POLICY = OFF;

--Step 2: (deny view to any database)

USE master;

GO

DENY VIEW ANY DATABASE TO MyUser;

--Step 3: (Authorized the user for that specific database)

USE master;

GO

ALTER AUTHORIZATION ON DATABASE::DatabaseName TO MyUser;

GO