Create SQL Server Login

In this article, we will show you the step by step approach to create SQL Server Login using SQL Server Management Studio, and Transact SQL query. Before we start creating new SQL login, let me show you the list of available logins in our SQL Server Management Studio

To create SQL Server login, please expand the Security folder, and right-click on the Login folder to open the context menu. Please select the New Login.. option to create a login

Once you choose the New Login.. option, the following window will open. By default, Windows Authentication selected so, let me change the login to SQL Server Authentication.

Login Name: Please use a unique name for this newly created SQL Server Login. It is the one that you are going to use to login to the Database Engine.
Password: Here, you have to specify the password for the above-specified username.
Enforce Password Policy: If you checkmark this option, your password should follow the standard SQL Server password policy.
Enforce Password Expiration: Password will expire after a certain period.
User Must Change Password at Next Login: When the user login with this newly created login credentials, SQL Server will ask to change the password.

We assigned the login name as John and some random password.

Default Database: Here, you have to select the default Database name that you want to assign for this user. For now, we are choosing the SQL Tutorial database.

Default Language: You can select the language as per your requirements. For now, we are leaving it default, which is English.

Server Roles: Use this page to assign the Server Roles to this User. We already explained everything about the Server roles in Create SQL Server Roles. For now, let me select the default Public option.

User Mapping: Use this SQL Server Login page to select the database, and assign database roles to this User. Since we selected the SQL tutorial as the default database, Login has automatically mapped the user to this database.

Again, We will write a dedicated article to explain each database role. So, for now, let me select the default Public and db_Owner option.

Securables: This page is used to select the Object. Please click on the Search button to search for Objects.

For now, let me select the Server

You can use the Grant, With Grant, and Deny options to grant permissions, or to deny. For now, we are leaving it without any changes.

Status: It has two options:

Permission to connect to database Engine: If you want to provide, then select Grant; otherwise, choose Deny.
Login: If you want this user to login to Database Manager, select Enabled otherwise, Disabled.

See our newly created Login in SQL Server Management Studio.

Please click on the Connect button to connect the Database Engine using John

Please change the Authentication mode from Windows Authentication to SQL Authentication and provide the Username and Password.

We had successfully logged into the database engine using our newly created login.

The basic syntax to create SQL login is:

-- Create SQL Login Syntax
CREATE LOGIN [Login Name] -- This is the User that you use for login 
WITH PASSWORD = 'provide_password' MUST_CHANGE,
CHECK_EXPIRATION = ON, -- This is Optional
CHECK_POLICY = ON, -- This is Optional
DEFAULT_DATABASE = [Database Name], -- This is Optional
DEFAULT_LANGUAGE = [Language Name];-- This is Optional

It will create a new login Mahesh with default settings.

-- Create SQL Login Example
CREATE LOGIN Mahesh
WITH PASSWORD = 'password'

It will show our newly created login

Please go to its properties to see or edit the login properties. As you can see, this log in is pointing to the master database.

It will create a new login Rob, and his default database is SQL tutorial, and the default language is English.

-- Create SQL Login Example
CREATE LOGIN Rob
WITH PASSWORD = 'password', 
DEFAULT_DATABASE = [SQL Tutorial], -- This is Optional
DEFAULT_LANGUAGE = [English];-- This is Optional

You can see the same in its properties.

The below Sql Server code snippet will create a new login Christy.

Her password must change when she starts login,
Password will expire after a certain period.
Given a password should meet the standard password policy.
And her default database is the SQL tutorial, and the default language is English.

-- Create SQL Login Example
CREATE LOGIN Christy
WITH PASSWORD = 'password' MUST_CHANGE, 
CHECK_EXPIRATION = ON, -- This is Optional
CHECK_POLICY = ON, -- This is Optional
DEFAULT_DATABASE = [SQL Tutorial], -- This is Optional
DEFAULT_LANGUAGE = [English];-- This is Optional

See the Christy in the logins section

You can see the login properties by going into their properties.

Let me try to connect with Christy

Once you click on the Connect button, SSMS will ask you to enter a new password. It is because we used the MUST_CHANGE in our query. It only happens if you checkmark the User Must Change Password at Next Login option also

To edit the existing Sql Server logins, Please go to the Logins folder to select the required Login name. Right-click on the login name and select Properties option.

Use this Login Properties window, to alter the Server Roles, User mappings, Status, Securables, etc.

Please go to the Logins folder to select the required Login name. Right-click on the name that you want to delete and select Delete option from it.