Share via

CREATE LOGIN (Transact-SQL)

  • Article

Creates a new SQL Server login.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

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

<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 

<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. If login_name is being mapped from a Windows domain account, login_name must be enclosed in square brackets ([ ]). See example D later in this topic.
  • WINDOWS
    Specifies that the login be mapped to a Windows login.
  • CERTIFICATE certificateName
    Specifies the name of a certificate to be associated with this login. This certificate must already exist 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 exist in the master database.
  • PASSWORD ='password'
    Applies to SQL Server logins only. Specifies the password for the login that is being created. This value may be already hashed. You should use a strong password. For more information see Strong Passwords.
  • 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. The HASHED option can only be used on logins for which the password policy checks are turned off. If this option is not selected, the string entered as password is hashed before being stored in the database.
  • 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.
  • SID = sid
    Applies to SQL Server logins only. Specifies the GUID of the new SQL Server login. If this option is not selected, SQL Server automatically assigns a GUID.
  • 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.

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.

Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.

Permissions

Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.

Examples

A. Creating a login with a password

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.

CREATE LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>' MUST_CHANGE;
GO

B. Creating a login mapped to a credential

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

CREATE LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>', 
    CREDENTIAL = <credential_name>;
GO

C. Creating a login from a certificate

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

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

D. Creating a login from a Windows domain account

The following example creates the login [Adventure-Works\Mary5] from a Windows domain account.

CREATE LOGIN [Adventure-Works\Mary5] FROM WINDOWS;
GO

See Also

Reference

ALTER LOGIN (Transact-SQL)
DROP LOGIN (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

Principals
Password Policy

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Clarified use of logins created from a certificate or asymmetric key.