CREATE LOGIN (Transact-SQL)

Creates a login for SQL Server, SQL Database, SQL Data Warehouse, or Parallel Data Warehouse databases. Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular version.

For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

Syntax

-- Syntax for SQL Server  
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }  

<option_list1> ::=   
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]  
    [ , <option_list2> [ ,... ] ]  

<option_list2> ::=    
    SID = sid  
    | DEFAULT_DATABASE = database      
    | DEFAULT_LANGUAGE = language  
    | CHECK_EXPIRATION = { ON | OFF}  
    | CHECK_POLICY = { ON | OFF}  
    | CREDENTIAL = credential_name   

<sources> ::=  
    WINDOWS [ WITH <windows_options>[ ,... ] ]  
    | CERTIFICATE certname  
    | ASYMMETRIC KEY asym_key_name  

<windows_options> ::=        
    DEFAULT_DATABASE = database  
    | DEFAULT_LANGUAGE = language  

Arguments

login_name
Specifies the name of the login that is created. There are four types of logins: SQL Server logins, Windows logins, certificate-mapped logins, and asymmetric key-mapped logins. When you are creating logins that are mapped from a Windows domain account, you must use the pre-Windows 2000 user logon name in the format [<domainName>\<login_name>]. You cannot use a UPN in the format login_name@DomainName. For an example, see example D later in this article. Authentication logins are type sysname and must conform to the rules for Identifiers and cannot contain a '\'. Windows logins can contain a '\'. Logins based on Active Directory users, are limited to names of less than 21 characters.

PASSWORD ='password*' Applies to SQL Server logins only. Specifies the password for the login that is being created. You should use a strong password. For more information, see Strong Passwords and Password Policy. Beginning with SQL Server 2012 (11.x),, stored password information is calculated using SHA-512 of the salted password.

Passwords are case-sensitive. Passwords should always be at least 8 characters long, and cannot exceed 128 characters. Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. Passwords cannot contain single quotes, or the login_name.

PASSWORD =hashed_password
Applies to the HASHED keyword only. Specifies the hashed value of the password for the login that is being created.

HASHED Applies to SQL Server logins only. Specifies that the password entered after the PASSWORD argument is already hashed. If this option is not selected, the string entered as password is hashed before it is stored in the database. This option should only be used for migrating databases from one server to another. Do not use the HASHED option to create new logins. The HASHED option cannot be used with hashes created by SQL 7 or earlier.

MUST_CHANGE Applies to SQL Server logins only. If this option is included, SQL Server prompts the user for a new password the first time the new login is used.

CREDENTIAL =credential_name
The name of a credential to be mapped to the new SQL Server login. The credential must already exist in the server. Currently this option only links the credential to a login. A credential cannot be mapped to the System Administrator (sa) login.

SID = sid
Used to recreate a login. Applies to SQL Server authentication logins only, not Windows authentication logins. Specifies the SID of the new SQL Server authentication login. If this option is not used, SQL Server automatically assigns a SID. The SID structure depends on the SQL Server version. SQL Server login SID: a 16 byte (binary(16)) literal value based on a GUID. For example, SID = 0x14585E90117152449347750164BA00A7.

DEFAULT_DATABASE =database
Specifies the default database to be assigned to the login. If this option is not included, the default database is set to master.

DEFAULT_LANGUAGE =language
Specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. If the default language of the server is later changed, the default language of the login remains unchanged.

CHECK_EXPIRATION = { ON | OFF }
Applies to SQL Server logins only. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.

CHECK_POLICY = { ON | OFF }
Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

If the Windows policy requires strong passwords, passwords must contain at least three of the following four characteristics:

  • An uppercase character (A-Z).
  • A lowercase character (a-z).
  • A digit (0-9).
  • One of the non-alphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.

WINDOWS
Specifies that the login be mapped to a Windows login.

CERTIFICATE certname
Specifies the name of a certificate to be associated with this login. This certificate must already occur in the master database.

ASYMMETRIC KEY asym_key_name
Specifies the name of an asymmetric key to be associated with this login. This key must already occur in the master database.

Remarks

  • Passwords are case-sensitive.
  • Prehashing of passwords is supported only when you are creating SQL Server logins.
  • If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
  • A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
  • When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.

Important

CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later. For more information, see Password Policy.

Permissions

After creating a login

After creating a login, the login can connect to SQL Server, but only has the permissions granted to the public role. Consider performing some of the following activities.

  • To connect to a database, create a database user for the login. For more information, see CREATE USER.

  • Create a user-defined server role by using CREATE SERVER ROLE. Use ALTER SERVER ROLEADD MEMBER to add the new login to the user-defined server role. For more information, see CREATE SERVER ROLE and ALTER SERVER ROLE.

  • Use sp_addsrvrolemember to add the login to a fixed server role. For more information, see Server-Level Roles and sp_addsrvrolemember.

  • Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. For more information, see GRANT.

Examples

A. Creating a login with a password

The following example creates a login for a particular user and assigns a password.

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  
GO  

B. Creating a login with a password that must be changed

The following example creates a login for a particular user and assigns a password. The MUST_CHANGE option requires users to change this password the first time they connect to the server.

Applies to: SQL Server 2008 through SQL Server 2017.

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>' 
    MUST_CHANGE,  CHECK_EXPIRATION = ON;
GO  

Note

The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

C. Creating a login mapped to a credential

The following example creates the login for a particular user, using the user. This login is mapped to the credential.

Applies to: SQL Server 2008 through SQL Server 2017.

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>',   
    CREDENTIAL = <credentialName>;  
GO  

D. Creating a login from a certificate

The following example creates login for a particular user from a certificate in master.

Applies to: SQL Server 2008 through SQL Server 2017.

USE MASTER;  
CREATE CERTIFICATE <certificateName>  
    WITH SUBJECT = '<login_name> certificate in master database',  
    EXPIRY_DATE = '12/05/2025';  
GO  
CREATE LOGIN <login_name> FROM CERTIFICATE <certificateName>;  
GO  

E. Creating a login from a Windows domain account

The following example creates a login from a Windows domain account.

Applies to: SQL Server 2008 through SQL Server 2017.

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;  
GO  

F. Creating a login from a SID

The following example first creates a SQL Server authentication login and determines the SID of the login.

CREATE LOGIN TestLogin WITH PASSWORD = 'SuperSecret52&&';  

SELECT name, sid FROM sys.sql_logins WHERE name = 'TestLogin';  
GO  

My query returns 0x241C11948AEEB749B0D22646DB1A19F2 as the SID. Your query will return a different value. The following statements delete the login, and then recreate the login. Use the SID from your previous query.

DROP LOGIN TestLogin;  
GO  

CREATE LOGIN TestLogin   
WITH PASSWORD = 'SuperSecret52&&', SID = 0x241C11948AEEB749B0D22646DB1A19F2;  

SELECT * FROM sys.sql_logins WHERE name = 'TestLogin';  
GO  

See Also