CREATE LOGIN (Transact-SQL) - Analytics Platform System (PDW)

Applies to: Analytics Platform System (PDW)

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

Transact-SQL syntax conventions

Syntax

-- Syntax for Analytics Platform System
CREATE LOGIN loginName { WITH <option_list1> | FROM WINDOWS }

<option_list1> ::=
    PASSWORD = { 'password' } [ MUST_CHANGE ]
    [ , <option_list> [ ,... ] ]
  
<option_list> ::=
      CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}

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're 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 can't 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 can't contain a '\'. Windows logins can contain a '\'. Logins based on Active Directory users, are limited to names of fewer than 21 characters.

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.

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.

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.

Remarks

  • Passwords are case-sensitive.
  • 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.

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.

After creating a login

After creating a login, the login can connect to Azure Synapse Analytics, 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

G. Create a SQL Server authentication login with a password

The following example creates the login Mary7 with password A2c3456.

CREATE LOGIN Mary7 WITH PASSWORD = 'A2c3456$#' ;

H. Use Options

The following example creates the login Mary8 with password and some of the optional arguments.

CREATE LOGIN Mary8 WITH PASSWORD = 'A2c3456$#' MUST_CHANGE,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON;

I. Create a login from a Windows domain account

The following example creates a login from a Windows domain account named Mary in the Contoso domain.

CREATE LOGIN [Contoso\Mary] FROM WINDOWS;
GO

Related content

  • Last updated on