CREATE LOGIN command in SQL Server

The CREATE LOGIN command is used to create a login account (Login) connecting to SQL Server. The login account will then be mapped to the user account (so before creating the user in SQL Server you must create a login account first).

There are 4 types of login accounts in SQL Server.

1. Login account uses Windows Authentication authentication.
2. Login account using SQL Server Authentication authentication.
3. Account login from certification.
4. Account login asymmetric keywords.

Syntax

Create a login account using Windows Authentication authentication

 CREATE LOGIN  [ten_mienten_dangnhap] 
 FROM WINDOWS 
 [ WITH DEFAULT_DATABASE = ten_cosodulieu 
 | DEFAULT_LANGU | DEFAULT_LANGU AGE = ten_ngonng]; 

Create a login account using SQL Server Authentication authentication

 CREATE LOGIN t en_dangnhap 
 WITH PASSWORD = { 'matkhau' | matkhau_bam HASHED } [ MUST_CHANGE] 
 [ , SID = giatri_duynhat 
  | DEFAULT_DATABASE = ten_cosodulieu 
  | DEFAULT_LANGUAGE = ten_ngonngu 
  | CHECK_EXPIRATION = { ON | OFF } 
  | CHECK_POLICY = { ON | OFF } 
  | CREDENTIAL = ten_c  | CREDENTIAL = ten_c hungthuc]; 

Create an account login from the certificate

 CREATE LOGIN 
 FROM CERTIFICATE te n_chungnhan; 

Create an account asymmetric keyword login

 CREATE LOG IN ten_dangnhap 
 FROM ASYMM ETRIC KEY ten_khoabatdoixung; 

Variable name or variable value

ten_mien

Name of the Windows domain account (Windows domain).

Network basics: Part 6 - Windows Domain

Username

Name of the login account.

ten_cosodulieu

Default database

ten_ngonngu

Default language for login account.

CHECK_EXPIRATION

The default is set to OFF. This option determines the password expiration policy. Must set CHECK_EXPIRATION = ON if using MUST_CHANGE.

matkhau

Password to login account.

matkhau_bam

The hash function value of the login password.

MUST_CHANGE

Used when forcing the password to change after the first login.

giatri_duynhat

The Globally Unique Identifier (GUID) of the login account. If omitted, SQL Server will specify the GUID for the account.

ten_chungthuc

Authentication name of the login account.

ten_chungnhan

Certificate name of the login account.

ten_khoabatdongxung

The name of the asymmetric key of the login account.

Note: See the ALTER LOGIN and DROP LOGIN commands.

Example using Windows Authentication authentication

 CREATE LOGIN [t est_tenmienquantrimang] 
 FROM WINDOWS ; 

This CREATE LOGIN command will create a new login account [test_tenmienquantrim] and use Windows authentication.

Example using SQL Server Authentication authentication

 CREATE LOGI N quantrimang 
 WITH PASSWOR D = 'mk123'; 

The above CREATE LOGIN command creates the login account quantrimang using SQL Server authentication and the password is mk123.

If you want to change the password after the first login, use the command below.

 CREATE LOGIN quantrimang 
 WITH PASSWORD = 'mk123' MUST_CHANGE, 
 CHECK_EXPIRATION = ON; 

Note that MUST_CHANGE cannot be used if CHECK_EXPIRATION is OFF. If you turn OFF, it will fail.

Example of using a certificate

 CREATE LOGIN quantrimang 
 FROM CERTIFICATE chungn han1; 

The above example creates a new Login named quantrimang, using the chungnhan1 certificate.

Example using asymmetric key

 CREATE LOGIN qu antrimang 
 FROM ASYMMETRI C KEY khoa_batdoixung1; 

The above CREATE LOGIN command creates a new Login quantrimang using the asymmetric lock____dodoungung1.

Previous article: Grant / Revoke permissions in SQL Server

The following article: ALTER LOGIN command in SQL Server