Create login in SQL Server

Last Updated : 20 Jun, 2024

Creating a login in SQL Server is a crucial step in securing your database and controlling access to it. A login is an account that allows a user to connect to the SQL Server instance and access the database.

In this article, we will explore the process of creating a login in SQL Server and the various options available to customize the login.

How to Create Login in SQL Server

Users with membership in the security-admin or sysadmin fixed server role can create logins on the server. There are different methods to create logins in SQL Server, depending on the requirements.

We will discuss 5 different scenarios and check how to create a login in SQL Server with syntax and examples.

Creating a login with a password

Syntax:

CREATE LOGIN <loginname> WITH PASSWORD = '<Password>';

Note: Passwords are case-sensitive.

Example:

Let's look at an example of creating a login for a particular user with a password. 

CREATE LOGIN geeks 
WITH PASSWORD = 'gEe@kF0rG##ks';

Creating a login with a password that has got to be changed

Syntax:

CREATE LOGIN <loginname> WITH PASSWORD = '<Password>'
MUST_CHANGE, CHECK_EXPIRATION = ON;

Example:

Let's look at an example of creating a login for a user with password. 

CREATE LOGIN geeks WITH PASSWORD = 'gEe@kF0rG##ks'
MUST_CHANGE, CHECK_EXPIRATION = ON;

Note:

The MUST_CHANGE option requires users to change this password the first time they connect to the server. The MUST_CHANGE option can't be used when CHECK_EXPIRATION is OFF.

Creating a login from a Windows domain account

Syntax:

CREATE LOGIN [<domainname>\<loginname>] 
FROM WINDOWS;

Example:

Let's look at an example of creating a login from a Windows domain account. 

CREATE LOGIN [AD\geeks] FROM WINDOWS;

Creating a login from a SID

Syntax:

CREATE LOGIN <loginname> 
WITH PASSWORD = '<Password>', 
SID = 0x241C11948AEEB749B0D22646DB1AXXXX;

Example:

Let's look at an example of creating a login from SID. 

CREATE LOGIN geeks 
WITH PASSWORD = 'gEe@kF0rG##ks', 
SID = 0x241C11948AEEB749B0D22646DB1AXXXX;

Creating a login with multiple arguments

Syntax:

CREATE LOGIN <loginname>
WITH PASSWORD = '<Password>',
DEFAULT_DATABASE = <Databasename>,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF ;

Example:

Let's look at an example of creating login using multiple arguments together. 

CREATE LOGIN geeks WITH PASSWORD = 'gEe@kF0rG##ks'
DEFAULT_DATABASE = GeeksDB,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF ;

Note:

A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported. Using the above mentioned methods you can create a login for SQL Server.

Comment

Explore