How to Create users in sql server for accessing only one database using Management Studio [closed]

Question

How to Create users in sql server 2008 R2 for accessing only one database using Management Studio.

regards, salman

Answer 1

Your answer is right here in SO already: Restrict SQL Server Login access to only one database. Just in case the link is broken:

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

You can add an user either using SSMS or by using CREATE USER. You could use sp_adduser but it's not recommended as it will be removed in a future version of Microsoft SQL Server.

You also need a login to match that user. Again, you can use SSMS or CREATE LOGIN for the task.

Check BOL's page for database roles on SQL Server 2008R2, use the dropdown menu on the top of that page to switch to other version if need to.

Here is the list of the roles that can be assigned:

db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

db_securityadmin Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.

db_accessadmin Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

Answer 2

In management studio, right click Security in object explorer and choose new login. Then, on the user mapping tab, you select which databases your login can access. (Login in at the server level, and a user is at the database level).