Export (0) Print
Expand All
Expand Minimize

CREATE LOGIN (Transact-SQL)

Creates a Database Engine login for SQL Server and Azure SQL Database.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

-- SQL Server Syntax
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
-- Syntax for Windows Azure SQL Database

CREATE LOGIN login_name
 { WITH <option_list3> }

<option_list3> ::= 
    PASSWORD = { 'password' }
    [ SID = sid ]

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 topic. SQL Server authentication logins are type sysname and must conform to the rules for Identifiers and cannot contain a '\'. Windows logins can contain a '\'.

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.

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: SQL Server 2008 through SQL Server 2014.

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 2008 through SQL Server 2014.

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 Server 7 or earlier,

MUST_CHANGE

Applies to: SQL Server 2008 through SQL Server 2014.

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

Applies to: SQL Server 2008 through SQL Server 2014.

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 sa login.

SID = sid

Applies to: SQL Server 2008 through SQL Server 2014.

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

Applies to: SQL Server 2008 through SQL Server 2014.

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

Applies to: SQL Server 2008 through SQL Server 2014.

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 2008 through SQL Server 2014.

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 2008 through SQL Server 2014.

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

Applies to: SQL Server 2008 through SQL Server 2014.

Specifies that the login be mapped to a Windows login.

CERTIFICATE certname

Applies to: SQL Server 2008 through SQL Server 2014.

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

Applies to: SQL Server 2008 through SQL Server 2014.

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

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 note 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.

For a script to transfer logins, see How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008.

Creating a login automatically enables the new login and grants the login the server level CONNECT SQL permission.

Windows Azure SQL Database Logins

In SQL Database, the CREATE LOGIN statement must be the only statement in a batch.

In some methods of connecting to SQL Database, such as sqlcmd, you must append the SQL Database server name to the login name in the connection string by using the <login>@<server> notation. For example, if your login is login1 and the fully qualified name of the SQL Database server is servername.database.windows.net, the username parameter of the connection string should be login1@servername. Because the total length of the username parameter is 128 characters, login_name is limited to 127 characters minus the length of the server name. In the example, login_name can only be 117 characters long because servername is 10 characters.

In SQL Database you must be connected to the master database to create a login.

SQL Server rules allow you create a SQL Server authentication login in the format <loginname>@<servername>. If your SQL Database server is myazureserver and your login is myemail@live.com, then you must supply your login as myemail@live.com@myazureserver.

For more information about SQL Database logins, see Managing Databases and Logins in Windows Azure SQL Database.

In SQL Server, requires ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role.

In SQL Database, only the server-level principal login (created by the provisioning process) or members of the loginmanager database role in the master database can create new logins.

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

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

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

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 2014.

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

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 2014.

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 2014.

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 2014.

CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft