How to create a user / add a user in SQL Management Studio / SQL Server

1. Remote onto the server

2. Open SQL Server Management Studio

 

 

 

3. Log onto the SQL Server using windows pass through authentication

 

 

 

4. Once logged onto the SQL Server, scroll to Security > Logins

 

 

 

5. Right click on Logins node and click on New Login

 

 

 

6. FOR ADDING USER WITH WINDOWS AUTHENTICATION FOLLOW THESE STEPS, FOR SQL AUTHENTICATION FOLLOW STEP 7

• Select General in the Select a Page section and make sure you chose Windows authentication and click on search

• Once you click on Search, you will be provided with the following window, choose the appropriate location.

• Add the appropriate user and click ok

• AFTER THIS STEP PLEASE FOLLOW STEP 8 AND ONWARDS

7. FOR ADDING USER WITH SQL AUTHENTICATION FOLLOW THESE STEPS

• Select General in the Select a Page section and make sure you chose SQL Server authentication

• Put in the desired login name and password in password as well as the confirm password section; Passwords should be at least 8 characters.

• Check Enforce policy; Uncheck Enforce password policy only if you want to turn off account lockout feature on multiple incorrect password attempts

8. Choose the Default database; if known at creation of account or leave the setting to be master

 

 

 

9. Choose User Mappings in the Select a page section and in the Users mapped to this login section, choose the appropriate database. Also in the Database role membership for: , choose the appropriate membership roles.

 

 

 

MORE INFORMATION ON ROLE MEMBERSHIP:

Database-level role name	Description
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.

IF YOU DO NOT KNOW WHAT ROLE TO CHOOSE, IT IS BEST TO CHOOSE DB_DATAREADER FOR USERS WHO WILL ONY READ DATA; ALSO IT IS BEST TO CHOOSE DB_DATAWRITER FOR USERS WHO WILL ONLY ADD, MODIFY AND DELETE DATA BUT NOT READ.

MOST USER ACCOUNTS ARE CREATED ARE USED FOR BOTH READING AND UPDATING DATA HENCE IN THIS CASE THE USER WILL HAVE TO HAVE DB_DATAREADER AND DB_DATAWRITER.

10. Choose Status in Select a page section; Make sure the permission to connect to database engine is Grant and Login is Enabled

 

 

 

11.Click OK; you are all set!

IN CASE YOU ENCOUNTER THIS ISSUE WHILE CREATING AN USER:

The issue is with the password and these are the rules of the password in SQL server:

The password must meet the Microsoft SQL Server Strong Password requirements:

Does not contain all or part of the user's account name

Is more than eight characters in length

Contains characters from at least three of the following categories:

English uppercase characters (A through Z)

English lowercase characters (a through z)

Base 10 digits (0 through 9)

Nonalphabetic characters (for example: !, $, #, %)