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:
|
|
|
|
|
|
|
|
