Restrict SQL Server Login access to only one database

Question

I have a SQL Server server which has around 50 databases on it.

I wish to create a new Login for a client who wishes to have access to their database.

But I don't want to give them access to the other 49 databases.

How can I do this?

Answer 1

I think this is what we like to do very much.

--Step 1: (create a new user) create LOGIN hello WITH PASSWORD='foo', CHECK_POLICY = OFF;   -- Step 2:(deny view to any database) USE master; GO DENY VIEW ANY DATABASE TO hello;     -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below) USE master; GO ALTER AUTHORIZATION ON DATABASE::yourDB TO hello; GO 

If you already created a user and assigned to that database before by doing

USE [yourDB]  CREATE USER hello FOR LOGIN hello WITH DEFAULT_SCHEMA=[dbo]  GO 

then kindly delete it by doing below and follow the steps

   USE yourDB;    GO    DROP USER newlogin;    GO 

For more information please follow the links:

Hiding databases for a login on Microsoft Sql Server 2008R2 and above