Create a New User in SQL Server

Managing SQL Server security is a critical part of maintaining your SQL Server environment. In this article, let us take a step-by-step approach to create a new database User and grant permissions to the User.

In SQL Server, a Login is created for the server instance whereas a User is created for a database. A Login has to be mapped to a User to connect to a database. A login can be mapped to only one user for any database. The scope of a User is the database

There are two ways to create a new User or to grant user permissions:

Using Microsoft SQL Server Management Studio
Using T-SQL

Create New User using SSMS

Open SQL Server management studio. In the Object Explorer, expand the Databases node.

Here, we are going to create a new user for the HR database. So, expand the HR database.

Expand the Security folder of the database and right click on User and select New User, as shown below.

Create a New User and Grant Permissions in SQL Server 2019

In the Database User - New dialog box, on the General tab page, User type drop down lists the following types:

SQL user with login
SQL user with password
SQL user without login
User mapped to a certificate
User mapped to an asymmetric key
Windows user

Select the User type as "SQL User with login" from the drop-down.

Under User name, enter a new user name. Here, we will enter ‘Steve' as a user name.

Under Login name, type the previously created login i.e., Steve. Alternately click on the ellipsis ... button to open the Select – Login dialog box.

Click on Browse button and select an existing login name to map with this user, as shown below. Select login ‘Steve'. Click the OK button to proceed.

The Owned Schema page lists all the available schemas in the database. To add schemas to the user, select the check box next to the schema name.

The Membership page lists all the possible membership roles that can be owned by the database user being created. To add membership roles, select the check-box next to the roles.

The Securables page lists all the securables and permissions that can be granted to the user. We will look at how to grant permissions to the User later in the article under User Permissions.

The Extended Properties page allows custom properties to be added to database users.

This page displays the name of the selected database and the collation used which are read-only fields. The page also has an option to view or specify the extended properties for the object.

Click OK to create this user.

Create a New User using T-SQL

Syntax:

USE <database-name>
CREATE USER <user-name> FOR LOGIN <login-name>

The following creates a new user Steve in the HR database:

SQL Script: Create New DB User

USE HR 
GO 
    CREATE USER 'Steve' FOR LOGIN 'Steve' 
GO