Creating Logins and Users in SQL Server

How to create a new SQL Server Login and User

Creating individual logins and users for SQL gives you a great security advantage.  A Login is used for authentication into a SQL Instance while a User is used for authorization into a SQL Database.  Note that Logins are used at the Instance level and Users are used at the Database level.  Here is how to create a new Login and User in SQL Server.


Here is an example using T-SQL

To create a Login, use the following script:

USE MASTER

GO

CREATE LOGIN NewLogin WITH PASSWORD=N’NewPassword1!’, DEFAULT_DATABASE = MASTER, DEFAULT_LANGUAGE = US_ENGLISH

GO

ALTER LOGIN NewLogin ENABLE

GO

Make sure to use the MASTER database when you create logins and make sure the login is ENABLED.

After creating a Login, you can now create a user and add the user to the new Login:

USE AdventureWorks2014

GO

CREATE USER NewUserName FOR LOGIN NewLogin WITH DEFAULT_SCHEMA = [DBO]

GO

Use can also use SQL Server’s GUI to create a Login and User as well.

Expand your instance and Security directory and right click on Logins.  Click on ‘New Login…’

The Login screen will be displayed.  Type in your new Login name along with a password.  You have options here.  You can chose to have the password be enforced by Windows rules or bypass them.  Please keep your own environment’s security in mind when deciding password rules.  Choose a default database as well and a default language.  Here, we left the language at ‘Default’ which is US English.

Once you are done, hit ‘OK’ to complete the process.

Next, expand the database you want to create a new user on.  Expand ‘Security’ and right click on ‘Users’ and click on ‘New User…’

Leave SQL user with login as the User type.  Specify a new User name.  Afterward, click on the browse icon next to the Login name text field.

Click on ‘Browse’.

Find the Login that you created earlier at your instance level.

Click on ‘OK’.

Back at the User window, click on ‘Default Schema’.  The Select Schema window will appear.  Click on Browse to select a schema for the user.

In this case we chose DBO as the default schema.

Click on ‘OK’ and then ‘OK’ again at the User window and you have successfully created a Login and User.

It is important to understand the security implications when you are creating Logins and Users.  Again, Logins allow authentication to get into the instance.  Users will be granted access to the databases that you created the Users in.  Additionally, it is important to understand what the user will have access to schema that you choose for that User.

Another detail to be aware of is since users get database access, you can create a User without a Login.  You can create Users with database and schema access to run scripts as that user and grant different actions to that user such as Select capability and use these Users as access and running tools for specific functionality.

One final thought: make sure you do not DROP a Login with Users still mapped to it.  If you do, this User will be “orphaned”, a User without a Login.  If this happens, you can ALTER the User and map it back to a Login with T-SQL:

ALTER /*orphaned_username*/ WITH LOGIN = /*valid_login*/

Logins and Users are vital for SQL Server to work and now you know how to create them.