How to Create New SQL Server Logins and Users

SQL Server Logins vs. Users: Understanding the Difference

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

Method 1: Create a SQL Server Login and User 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

Step 1: Create the Login in the MASTER Database 

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

Step 2: Create the Database User and Map It to the Login 

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

Method 2: Create a SQL Server Login and User Using the SSMS GUI 

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

This method uses SQL Server Management Studio (SSMS), the standard management tool for SQL Server — if you’re new to SSMS, that linked post also covers a handy tip for refreshing the IntelliSense cache after creating new database objects.

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.

Understanding Orphaned Users and How to Fix Them 

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.

SQL Server Security and Access Management Support from Concurrency

Logins and Users are vital for SQL Server to work, and now you know how to create them — but managing access at scale across an enterprise environment takes more than a one-time setup. If you’re looking to strengthen data governance and access control across your SQL Server and broader Azure data estate, Concurrency can help you build a security model that scales with your organization.