CREATE LOGIN (Transact-SQL)

Creates a login for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, or Analytics Platform System databases. Choose one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular version.

CREATE LOGIN participates in transactions. If CREATE LOGIN is executed within a transaction and the transaction is rolled back, then login creation is rolled back. If executed within a transaction, the created login cannot be used until the transaction is committed.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

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

Select a product

In the following row, select the product name you're interested in, and only that product's information is displayed.

* SQL Server *  

Azure SQL Database

Azure SQL
Managed Instance

Azure Synapse
Analytics

Analytics Platform
System (PDW)

 

SQL Server

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>[ ,... ] ]
    | EXTERNAL PROVIDER
    | 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 five types of logins: SQL Server logins, Windows logins, Microsoft Entra logins, certificate-mapped logins, and asymmetric key-mapped logins.

When you're creating logins that are mapped from a Windows domain account, you must use the logon name in the format [<domainName>\<login_name>]. You cannot use a UPN in the format login_name@DomainName. For an example, see example E later in this article. Authentication logins are type sysname and must conform to the rules for Identifiers and cannot contain a backslash (). Windows logins can contain a '\'. Logins based on Active Directory users are limited to names of fewer than 21 characters.

When using the FROM EXTERNAL PROVIDER clause, the login name must match the display name of an existing Microsoft Entra principal in the same tenant that the SQL instance is Arc-enabled to. Microsoft Entra users, groups, and applications can be used to create logins.

PASSWORD ='password'

Applies to SQL Server logins only. Specifies the password for the login that is being created. 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 eight characters long, and can't exceed 128 characters. Passwords can include a-z, A-Z, 0-9, and most nonalphanumeric characters. Passwords can't 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 isn't selected, the string entered as password is hashed before it's stored in the database. This option should only be used for migrating databases from one server to another. Don't use the HASHED option to create new logins. The HASHED option can't 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 can't 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 isn't included, the default database is set to master.

DEFAULT_LANGUAGE =language

Specifies the default language to be assigned to the login. If this option isn't 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 nonalphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.

WINDOWS

Specifies that the login be mapped to a Windows login.

FROM EXTERNAL PROVIDER

Specifies that the login is mapped to a Microsoft Entra principal. This option is available for Arc-enabled SQL Server 2022 and later versions. For more information, see Microsoft Entra authentication for SQL Server

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're 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 isn't 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 versions. For more information, see Password Policy.

• Logins created from certificates or asymmetric keys are used only for code signing. They can't 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.
• The server's authentication mode must match the login type to permit access.
• For information about designing a permissions system, see Getting Started with Database Engine Permissions.

Permissions

• Only users with ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role can create logins. For more information, see Server-Level Roles and ALTER SERVER ROLE.
• If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.

Permissions for SQL Server 2022 and later

Requires CREATE LOGIN permission on the server or membership in the ##MS_LoginManager## fixed server role.

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 ROLE ... ADD 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. Create 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. Create 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 (10.0.x) and later versions.

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. Create 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 (10.0.x) and later versions.

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

D. Create a login from a certificate

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

Applies to: SQL Server 2008 (10.0.x) and later versions.

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. Create a login from a Windows domain account

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

Applies to: SQL Server 2008 (10.0.x) and later versions.

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

F. Create 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

G. Create a login with multiple arguments

The following example shows how to string multiple arguments together using commas between each argument.

CREATE LOGIN [MyUser]
WITH PASSWORD = 'MyPassword',
DEFAULT_DATABASE = MyDatabase,
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF ;

H. Create a SQL login with hashed password

The following example shows how to create SQL Logins with the same password as existing Logins as done in a migration scenario. The first step is to retrieve the password hash from existing Logins on the source database server. Then the same hash will be used to create the Login on a new database server. By doing this the new Login will have the same password as on the old server.

-- run this to retrieve the password hash for an individual Login:
SELECT LOGINPROPERTY('Andreas','PASSWORDHASH') AS password_hash;
-- as an alternative, the catalog view sys.sql_logins can be used to retrieve the password hashes for multiple accounts at once. (This could be used to create a dynamic sql statement from the result set
SELECT name, password_hash
FROM sys.sql_logins
  WHERE
    principal_id > 1    -- excluding sa
    AND
    name NOT LIKE '##MS_%##' -- excluding special MS system accounts
-- create the new SQL Login on the new database server using the hash of the source server
CREATE LOGIN Andreas
  WITH PASSWORD = 0x02000A1A89CD6C6E4C8B30A282354C8EA0860719D5D3AD05E0CAE1952A1C6107A4ED26BEBA2A13B12FAB5093B3CC2A1055910CC0F4B9686A358604E99BB9933C75B4EA48FDEA HASHED;

I. Create a login for a Microsoft Entra user

The following example creates a login for the Microsoft Entra account joe@contoso.onmicrosoft.com that exists in the Microsoft Entra tenant named contoso.

CREATE LOGIN [joe@contoso.onmicrosoft.com] FROM EXTERNAL PROVIDER

GO

J. Create a login for a federated Microsoft Entra account

The following example creates a login for a federated Microsoft Entra account bob@contoso.com that exists in a tenant called contoso. User bob can also be a guest user.

CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER
GO

K. Create a login for a Microsoft Entra group

The following example creates a login for the Microsoft Entra group mygroup that exists in the tenant contoso.

CREATE LOGIN [mygroup] FROM EXTERNAL PROVIDER
GO

L. Create a login for a Microsoft Entra application

The following example creates a login for the Microsoft Entra application myapp that exists in the tenant contoso.

CREATE LOGIN [myapp] FROM EXTERNAL PROVIDER

Related content

• Getting Started with Database Engine Permissions
• Principals
• Password Policy
• ALTER LOGIN
• DROP LOGIN
• EVENTDATA
• Create a Login