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?

Solution 1

1. Connect to your SQL server instance using management studio
2. Goto Security -> Logins -> (RIGHT CLICK) New Login
3. fill in user details
4. Under User Mapping, select the databases you want the user to be able to access and configure

---

UPDATE:

You'll also want to goto Security -> Server Roles, and for public check the permissions for TSQL Default TCP/TSQL Default VIA/TSQL Local Machine/TSQL Named Pipesand remove the connect permission

Solution 2

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

Solution 3

For anyone else out there wondering how to do this, I have the following solution for SQL Server 2008 R2 and later:

USE master
go
DENY VIEW ANY DATABASE TO [user]
go

This will address exactly the requirement outlined above..

Solution 4

this is to topup to what was selected as the correct answer. It has one missing step that when not done, the user will still be able to access the rest of the database. First, do as @DineshDB suggested

  1. Connect to your SQL server instance using management studio
   2. Goto Security -> Logins -> (RIGHT CLICK) New Login
   3. fill in user details
   4. Under User Mapping, select the databases you want the user to be able to access and configure

the missing step is below:

5. Under user mapping, ensure that "sysadmin" is NOT CHECKED and select "db_owner" as the role for the new user.

And thats it.

View more solutions

Comments

[Curtis]

Thanls for your reply. I've created a new user this way, tried logging in with them in SSMS2008 and I have full access to all databases...

[Irfy]

Hi Curt - It's going to be something to do with the default access settings via either public or guest on both a server level and database level. Please try the above and if that doesn't work there are a couple other things i can suggest (but the last time I had this problem that fixed it)

[recursive_acronym]

Make sure the user in question isn't set as a 'sysadmin' in their properties.

[BiLaL]

This command will block access to ALL of the 50 databases, please provide the other required statements also...

[Bernardo Dal Corno]

I have several users not mapped to the database but still with access. I think this is equal to "has login inside database users"

[Bernardo Dal Corno]

Now, how would you do if you can't DENY all databases first? The oposite, of that, only restrict access to one specific database.

[Wayne]

DENY VIEW ANY DATABASE TO suggesteventsusers; Is saying that it is not correct SQL syntax for me.