CREATE LOGIN (Transact-SQL)CREATE LOGIN (Transact-SQL)

为 SQL Server、SQL 数据库、SQL 数据仓库或 Analytics Platform System 数据库创建登录名。Creates a login for SQL Server, SQL Database, SQL Data Warehouse, or Analytics Platform System databases. 单击以下选项卡之一,了解特定版本的语法、参数、注解、权限和示例。Click one of the following tabs for the syntax, arguments, remarks, permissions, and examples for a particular version.

CREATE LOGIN 参与事务。CREATE LOGIN participates in transactions. 如果在事务内执行 CREATE LOGIN 并且该事务回滚,则登录名创建也会回滚。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.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

单击一个产品!Click a product!

在下一行中,单击你感兴趣的产品名称。In the following row, click whichever product name you are interested in. 单击时此网页上的此位置会显示适合你单击的任何产品的不同内容。The click displays different content here on this webpage, appropriate for whichever product you click.

* SQL Server *  * SQL Server *   SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

SQL ServerSQL 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>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

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

参数Arguments

login_name 指定创建的登录名。login_name Specifies the name of the login that is created. 有四种类型的登录名:SQL Server 登录名、Windows 登录名、证书映射登录名以及非对称密钥映射登录名。There are four types of logins: SQL Server logins, Windows logins, certificate-mapped logins, and asymmetric key-mapped logins. 在创建从 Windows 域帐户映射的登录名时,必须以 [<domainName>\<login_name>] 格式使用 Windows 2000 之前的用户登录名。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>]. 不能使用 login_name@DomainName 格式的 UPN。You cannot use a UPN in the format login_name@DomainName. 有关示例,请参阅本文后面的示例 D。For an example, see example D later in this article. 身份验证登录的类型为 sysname,它必须符合标识符规则,且不能包含“\” 。Authentication logins are type sysname and must conform to the rules for Identifiers and cannot contain a '\'. Windows 登录名可以包含“\” 。Windows logins can contain a '\'. 基于 Active Directory 用户的登录名的名称限制为少于 21 个字符。Logins based on Active Directory users, are limited to names of fewer than 21 characters.

PASSWORD =“password”仅适用于 SQL Server 登录名 。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. 从 SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

密码是区分大小写的。Passwords are case-sensitive. 密码应始终至少包含八个字符,并且不能超过 128 个字符。Passwords should always be at least eight characters long, and cannot exceed 128 characters. 密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. 密码不能包含单引号或 login_name 。Passwords cannot contain single quotes, or the login_name.

PASSWORD = hashed_password 仅适用于 HASHED 关键字。PASSWORD = hashed_password Applies to the HASHED keyword only. 指定要创建的登录名的密码的哈希值。Specifies the hashed value of the password for the login that is being created.

HASHED 仅适用于 SQL Server 登录。HASHED Applies to SQL Server logins only. 指定在 PASSWORD 参数后输入的密码已经过哈希运算。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. 切勿使用 HASHED 选项创建新的登录名。Do not use the HASHED option to create new logins. HASHED 选项不能用于 SQL 7 或更早版本创建的哈希。The HASHED option cannot be used with hashes created by SQL 7 or earlier.

MUST_CHANGE 仅适用于 SQL Server 登录。MUST_CHANGE Applies to SQL Server logins only. 如果包括此选项,则 SQL Server 将在首次使用新登录时提示用户输入新密码。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 将映射到新 SQL Server 登录名的凭据名称。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. 凭据不能映射到系统管理员 (sa) 登录名。A credential cannot be mapped to the System Administrator (sa) login.

SID = sid 用于重新创建登录名。SID = sid Used to recreate a login. 仅适用于 SQL Server 身份验证登录,不适用于 Windows 身份验证登录。Applies to SQL Server authentication logins only, not Windows authentication logins. 指定新 SQL Server 身份验证登录的 SID。Specifies the SID of the new SQL Server authentication login. 如果未使用此选项,SQL Server 将自动分配 SID。If this option is not used, SQL Server automatically assigns a SID. SID 结构取决于 SQL Server 版本。The SID structure depends on the SQL Server version. SQL Server 登录 SID:基于 GUID 的 16 字节 (binary(16)) 文本值 。SQL Server login SID: a 16 byte (binary(16)) literal value based on a GUID. 例如, SID = 0x14585E90117152449347750164BA00A7For example, SID = 0x14585E90117152449347750164BA00A7.

DEFAULT_DATABASE = database 指定将指派给登录名的默认数据库。DEFAULT_DATABASE =database Specifies the default database to be assigned to the login. 如果未包括此选项,则默认数据库将设置为 master。If this option is not included, the default database is set to master.

DEFAULT_LANGUAGE = language 指定将指派给登录名的默认语言。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 } 仅适用于 SQL Server 登录名。CHECK_EXPIRATION = { ON | OFF } Applies to SQL Server logins only. 指定是否应对此登录帐户强制实施密码过期策略。Specifies whether password expiration policy should be enforced on this login. 默认值为 OFF。The default value is OFF.

CHECK_POLICY = { ON | OFF } 仅适用于 SQL Server 登录名。CHECK_POLICY = { ON | OFF } Applies to SQL Server logins only. 指定应对此登录强制实施运行 SQL Server 的计算机的 Windows 密码策略。Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. 默认值为 ON。The default value is ON.

如果 Windows 策略要求强密码,密码必须至少包含以下四个特点中的三个:If the Windows policy requires strong passwords, passwords must contain at least three of the following four characteristics:

  • 大写字符 (A-Z)。An uppercase character (A-Z).
  • 小写字符 (a-z)。A lowercase character (a-z).
  • 数字 (0-9)。A digit (0-9).
  • 一个非字母数字字符,如空格、_、@、*、^、%、!、$、# 或 &。One of the non-alphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.

WINDOWS 指定将登录名映射到 Windows 登录名。WINDOWS Specifies that the login be mapped to a Windows login.

CERTIFICATE certname 指定将与此登录名关联的证书名称。CERTIFICATE certname Specifies the name of a certificate to be associated with this login. 此证书必须已存在于 master 数据库中。This certificate must already occur in the master database.

ASYMMETRIC KEY asym_key_name 指定将与此登录名关联的非对称密钥的名称。ASYMMETRIC KEY asym_key_name Specifies the name of an asymmetric key to be associated with this login. 此密钥必须已存在于 master 数据库中。This key must already occur in the master database.

RemarksRemarks

  • 密码是区分大小写的。Passwords are case-sensitive.
  • 只有创建 SQL Server 登录时,才支持对密码预先进行哈希运算。Prehashing of passwords is supported only when you are creating SQL Server logins.
  • 如果指定 MUST_CHANGE,则 CHECK_EXPIRATION 和 CHECK_POLICY 必须设置为 ON。If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. 否则,该语句将失败。Otherwise, the statement will fail.
  • 不支持 CHECK_POLICY = OFF 和 CHECK_EXPIRATION = ON 的组合。A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
  • 如果 CHECK_POLICY 设置为 OFF,将对 lockout_time 进行重置,并将 CHECK_EXPIRATION 设置为 OFF 。When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.

重要

只有在 Windows Server 2003 及更高版本上才会强制执行 CHECK_EXPIRATION 和 CHECK_POLICY。CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later. 有关详细信息,请参阅 Password PolicyFor more information, see Password Policy.

权限Permissions

  • 只有具有针对服务器的 ALTER ANY LOGIN 权限或 securityadmin 固定服务器角色的成员身份的用户才可创建登录 。Only users with ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role can create logins. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLEFor more information, see Server-Level Roles and ALTER SERVER ROLE.
  • 如果使用 CREDENTIAL 选项,则还需要对此服务器的 ALTER ANY CREDENTIAL 权限 。If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server.

创建登录后After creating a login

创建登录后,该登录可以连接到 SQL Server,但是只具有授予 public 角色的权限 。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. 有关详细信息,请参阅 CREATE USERFor more information, see CREATE USER.
  • 使用 CREATE SERVER ROLE 创建用户定义的服务器角色。Create a user-defined server role by using CREATE SERVER ROLE. 使用 ALTER SERVER ROLE ... ADD MEMBER 将新登录名添加到用户定义的服务器角色 。Use ALTER SERVER ROLE ... ADD MEMBER to add the new login to the user-defined server role. 有关详细信息,请参阅 CREATE SERVER ROLEALTER SERVER ROLEFor more information, see CREATE SERVER ROLE and ALTER SERVER ROLE.
  • 使用 sp_addsrvrolemember 将登录名添加到固定服务器角色 。Use sp_addsrvrolemember to add the login to a fixed server role. 有关详细信息,请参阅服务器级别角色sp_addsrvrolememberFor more information, see Server-Level Roles and sp_addsrvrolemember.
  • 使用 GRANT 语句将服务器级别权限授予新的登录名或包含该登录名的角色 。Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. 有关详细信息,请参阅 GRANTFor more information, see GRANT.

示例Examples

A.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.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. MUST_CHANGE 选项要求用户在首次连接服务器时更改此密码。The MUST_CHANGE option requires users to change this password the first time they connect to the server.

适用范围SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.

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

备注

当 CHECK_EXPIRATION 设为 OFF (关)时,不能使用 MUST_CHANGE 选项。The MUST_CHANGE option cannot be used when CHECK_EXPIRATION is OFF.

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

适用范围SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.

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

D.D. 从证书创建登录名Creating a login from a certificate

下例用 master 中的证书为特定用户创建登录名。The following example creates login for a particular user from a certificate in master.

适用范围SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.

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.E. 从 Windows 域帐户创建登录名Creating a login from a Windows domain account

以下示例使用 Windows 域帐户创建一个登录名。The following example creates a login from a Windows domain account.

适用范围SQL Server 2008SQL Server 2008SQL Server 2019SQL Server 2019Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019SQL Server 2019.

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

F.F. 从 SID 创建登录名Creating a login from a SID

以下示例首先创建 SQL Server 身份验证登录,并确定该登录的 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

我的查询返回 0x241C11948AEEB749B0D22646DB1A19F2 作为 SID。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. 使用前面的查询中的 SID。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

SQL ServerSQL Server *SQL 数据库
单一数据库/弹性池*
* SQL Database
single database/elastic pool *
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL 数据库单一数据库/弹性池Azure SQL Database single database/elastic pool

语法Syntax

-- Syntax for Azure SQL Database
CREATE LOGIN login_name
 { WITH <option_list> }

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

参数Arguments

login_name 指定创建的登录名。login_name Specifies the name of the login that is created. Azure SQL 数据库单一数据库/弹性池仅支持 SQL 登录名。Azure SQL Database single database/elastic pool supports only SQL logins.

PASSWORD =' password*' 指定正在创建的 SQL 登录名的密码。PASSWORD =' password*' Specifies the password for the SQL login that is being created. 请使用强密码。Use a strong password. 有关详细信息,请参阅强密码密码策略For more information, see Strong Passwords and Password Policy. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

密码是区分大小写的。Passwords are case-sensitive. 密码应始终至少包含八个字符,并且不能超过 128 个字符。Passwords should always be at least eight characters long, and cannot exceed 128 characters. 密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. 密码不能包含单引号或 login_name 。Passwords cannot contain single quotes, or the login_name.

SID = sid 用于重新创建登录名。SID = sid Used to recreate a login. 仅适用于 SQL Server 身份验证登录,不适用于 Windows 身份验证登录。Applies to SQL Server authentication logins only, not Windows authentication logins. 指定新 SQL Server 身份验证登录的 SID。Specifies the SID of the new SQL Server authentication login. 如果未使用此选项,SQL Server 将自动分配 SID。If this option is not used, SQL Server automatically assigns a SID. SID 结构取决于 SQL Server 版本。The SID structure depends on the SQL Server version. 对于 SQL 数据库,这是包含 0x01060000000000640000000000000000 的 32 字节 (binary(32)) 文本以及表示 GUID 的 16 个字节 。For SQL Database, this is a 32 byte (binary(32)) literal consisting of 0x01060000000000640000000000000000 plus 16 bytes representing a GUID. 例如, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7For example, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7.

RemarksRemarks

登录Login

SQL 数据库登录名SQL Database Logins

CREATE LOGIN 语句必须是批中的唯一语句 。The CREATE LOGIN statement must be the only statement in a batch.

在连接到 SQL 数据库的一些方法(如 sqlcmd)中,必须使用 <login>@<server> 符号将 SQL 数据库服务器名称追加到连接字符串中的登录名之后 。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. 例如,如果登录为 login1,SQL 数据库服务器的完全限定名称是 servername.database.windows.net,则连接字符串的 username 参数应是 login1@servernameFor 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. 由于 username 参数的总长度为 128 个字符,因此,login_name 被限定为 127 个字符减去服务器名称的长度 。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. 在示例中,login_name 只能包含 117 个字符,因为 servername 包含 10 个字符。In the example, login_name can only be 117 characters long because servername is 10 characters.

在 SQL 数据库中,必须连接到 master 数据库才能创建登录。In SQL Database, you must be connected to the master database to create a login.

SQL Server 规则允许你创建 <loginname>@<servername> 格式的 SQL Server 身份验证登录。SQL Server rules allow you create a SQL Server authentication login in the format <loginname>@<servername>. 如果你的 SQL 数据库SQL Database服务器是 myazureserver 并且登录名是 myemail@live.com,则必须提供 myemail@live.com@myazureserver 格式的登录名 。If your SQL 数据库SQL Database server is myazureserver and your login is **myemail@live.com**, then you must supply your login as **myemail@live.com@myazureserver**.

在 SQL 数据库中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL Database, login data required to authenticate a connection and server-level firewall rules is temporarily cached in each database. 此缓存定期刷新。This cache is periodically refreshed. 若要强制刷新身份验证缓存并确保数据库具有最新版本的登录表,请执行 DBCC FLUSHAUTHCACHETo force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.

有关 SQL 数据库登录的详细信息,请参阅管理 Azure SQL 数据库中的数据库和登录For more information about SQL Database logins, see Managing Databases and Logins in Azure SQL Database.

权限Permissions

只有服务器级别主体登录(由预配过程创建)或 master 数据库中的 loginmanager 数据库角色成员可以创建新的登录。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. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLEFor more information, see Server-Level Roles and ALTER SERVER ROLE.

登录名Logins

  • 必须具有对服务器的 ALTER ANY LOGIN 权限或 securityadmin 固定服务器角色的成员身份 。Must have ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role. 只有具有针对服务器的 ALTER ANY LOGIN 权限或 securityadmin 权限的成员身份的 Azure Active Directory (Azure AD) 帐户可以执行此命令 Only Azure Active Directory (Azure AD) account with ALTER ANY LOGIN permission on the server or membership in the securityadmin permission can execute this command
  • 必须是用于 Azure SQL 数据库服务器的同一目录中的 Azure AD 成员Must be a member of Azure AD within the same directory used for Azure SQL Database server

创建登录后After creating a login

创建登录后,该登录可以连接到 SQL 数据库,但是只具有授予 public 角色的权限 。After creating a login, the login can connect to SQL Database 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 in that database. 有关详细信息,请参阅 CREATE USERFor more information, see CREATE USER.
  • 若要向数据库中的用户授予权限,请使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个内置数据库角色或自定义角色中,或者使用 GRANT 语句直接向用户授予权限 。To grant permissions to a user in a database, use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the built-in database roles or a custom role, or grant permissions to the user directly using the GRANT statement. 有关详细信息,请参阅非管理员角色其他服务器级管理角色ALTER SERVER ROLEGRANT 语句。For more information, see Non-administrator Roles, Additional server-level administrative roles, ALTER SERVER ROLE, and GRANT statement.
  • 若要授予服务器范围内的权限,请在 master 数据库中创建数据库用户,并使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个管理服务器角色。To grant server-wide permissions, create a database user in the master database and use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the administrative server roles. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLE服务器角色For more information, see Server-Level Roles and ALTER SERVER ROLE, and Server roles.
  • 使用 GRANT 语句将服务器级别权限授予新的登录名或包含该登录名的角色 。Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. 有关详细信息,请参阅 GRANTFor more information, see GRANT.

示例Examples

A.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.B. 从 SID 创建登录名Creating a login from a SID

以下示例首先创建 SQL Server 身份验证登录,并确定该登录的 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

我的查询返回 0x241C11948AEEB749B0D22646DB1A19F2 作为 SID。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. 使用前面的查询中的 SID。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

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
*SQL 数据库
托管实例*
* SQL Database
managed instance *
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL 数据库托管实例Azure SQL Database managed instance

语法Syntax

-- Syntax for Azure SQL Database managed instance
CREATE LOGIN login_name [FROM EXTERNAL PROVIDER] { WITH <option_list> [,..]}

<option_list> ::=
    PASSWORD = {'password'}
    | SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

参数Arguments

login_name 与 FROM EXTERNAL PROVIDER 子句一起使用时,登录名指定 Azure Active Directory (AD) 主体,可以是 Azure AD 用户、组或应用程序。login_name When used with the FROM EXTERNAL PROVIDER clause, the login specifies the Azure Active Directory (AD) Principal, which is an Azure AD user, group, or application. 否则,登录名表示所创建 SQL 登录名的名称。Otherwise, the login represents the name of the SQL login that was created.

FROM EXTERNAL PROVIDERFROM EXTERNAL PROVIDER
指定登录名用于 Azure AD 身份验证。Specifies that the login is for Azure AD Authentication.

PASSWORD = 'password ' 指定正在创建的 SQL 登录名的密码。PASSWORD = 'password' Specifies the password for the SQL login that is being created. 请使用强密码。Use a strong password. 有关详细信息,请参阅强密码密码策略For more information, see Strong Passwords and Password Policy. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

密码是区分大小写的。Passwords are case-sensitive. 密码应始终至少包含八个字符,并且不能超过 128 个字符。Passwords should always be at least eight characters long, and cannot exceed 128 characters. 密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. 密码不能包含单引号或 login_name 。Passwords cannot contain single quotes, or the login_name.

SID = sid 用于重新创建登录名。SID = sid Used to recreate a login. 仅适用于 SQL Server 身份验证登录名。Applies to SQL Server authentication logins only. 指定新 SQL Server 身份验证登录的 SID。Specifies the SID of the new SQL Server authentication login. 如果未使用此选项,SQL Server 将自动分配 SID。If this option is not used, SQL Server automatically assigns a SID. SID 结构取决于 SQL Server 版本。The SID structure depends on the SQL Server version. 对于 SQL 数据库,这是包含 0x01060000000000640000000000000000 的 32 字节 (binary(32)) 文本以及表示 GUID 的 16 个字节 。For SQL Database, this is a 32 byte (binary(32)) literal consisting of 0x01060000000000640000000000000000 plus 16 bytes representing a GUID. 例如, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7For example, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7.

RemarksRemarks

  • 密码是区分大小写的。Passwords are case-sensitive.

  • 创建映射到 Azure AD 帐户的服务器级主体 (FROM EXTERNAL PROVIDER) 时引入了新语法 New syntax is introduced for the creation of server-level principals mapped to Azure AD accounts (FROM EXTERNAL PROVIDER)

  • 指定 FROM EXTERNAL PROVIDER 时 :When FROM EXTERNAL PROVIDER is specified:

    • login_name 必须表示现有 Azure AD 帐户(用户、组或应用程序),当前 Azure SQL 托管实例可在 Azure AD 中访问该帐户。The login_name must represent an existing Azure AD account (user, group, or application) that is accessible in Azure AD by the current Azure SQL managed instance. 对于 Azure AD 主体,CREATE LOGIN 语法需要:For Azure AD principals, the CREATE LOGIN syntax requires:
      • Azure AD 用户的 Azure AD 对象的 UserPrincipalName。UserPrincipalName of the Azure AD object for Azure AD Users.
      • Azure AD 组和 Azure AD 应用程序的 Azure AD 对象的 DisplayName。DisplayName of Azure AD object for Azure AD Groups and Azure AD Applications.
    • 不能使用 PASSWORD 选项 。The PASSWORD option cannot be used.
  • 默认情况下,忽略 FROM EXTERNAL PROVIDER 子句时,会创建一个常规 SQL 登录名 。By default, when the FROM EXTERNAL PROVIDER clause is omitted, a regular SQL login is created.

  • Azure AD 登录名会在 sys.server_principals 中显示,同时,对于映射到 Azure AD 用户的登录名,类型列值设置为“E”,type_desc 设置为“EXTERNAL_LOGIN”;对于映射到 Azure AD 组的登录名,类型列值设置为“X”,type_desc 设置为“EXTERNAL_GROUP” 。Azure AD logins are visible in sys.server_principals, with type column value set to E and type_desc set to EXTERNAL_LOGIN for logins mapped to Azure AD users, or type column value set to X and type_desc value set to EXTERNAL_GROUP for logins mapped to Azure AD groups.

  • 有关用于传输登录名的脚本,请参阅如何在 SQL Server 2005 和 SQL Server 2008 的实例之间传输登录名和密码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.

  • 自动创建登录名将启用新的登录名,并授予它服务器级 CONNECT SQL 权限 。Creating a login automatically enables the new login and grants the login the server level CONNECT SQL permission.

登录名和权限Logins and Permissions

只有服务器级别主体登录(由预配过程创建)或 master 数据库中的 securityadminsysadmin 数据库角色成员可以创建新的登录名。Only the server-level principal login (created by the provisioning process) or members of the securityadmin or sysadmin database role in the master database can create new logins. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLEFor more information, see Server-Level Roles and ALTER SERVER ROLE.

默认情况下,在 master 中授予新建 Azure AD 登录名的标准权限为:CONNECT SQL 和 VIEW ANY DATABASE 。By default, the standard permission granted to a newly created Azure AD login in master is: CONNECT SQL and VIEW ANY DATABASE.

SQL 数据库托管实例登录名SQL Database managed instance Logins

  • 必须具有对服务器的 ALTER ANY LOGIN 权限或固定服务器角色(securityadminsysadmin 中的一个)的成员身份 。Must have ALTER ANY LOGIN permission on the server or membership in the one of the fixed server roles securityadmin or sysadmin. 只有具有针对服务器的 ALTER ANY LOGIN 权限或其中一个角色的成员身份的 Azure Active Directory (Azure AD) 帐户可以执行创建命令 。Only an Azure Active Directory (Azure AD) account with ALTER ANY LOGIN permission on the server or membership in one of those roles can execute the create command.
  • 如果登录名是 SQL 主体,那么只有属于 sysadmin 角色的登录才能使用创建命令为 Azure AD 帐户创建登录名。If the login is a SQL Principal, only logins that are part of the sysadmin role can use the create command to create logins for an Azure AD account.
  • 必须是用于 Azure SQL 托管实例的同一目录中的 Azure AD 成员。Must be a member of Azure AD within the same directory used for Azure SQL managed instance.

创建登录后After creating a login

备注

创建之后用于托管实例的 Azure AD 管理员功能已更改。The Azure AD admin for managed instance functionality after creation has changed. 有关详细信息,请参阅用于 MI 的新 Azure AD 管理员功能For more information, see New Azure AD admin functionality for MI.

创建登录名后,该登录名可以连接到 SQL 数据库托管实例,但只具有授予 public 角色的权限。After creating a login, the login can connect to a SQL Database managed instance, but only has the permissions granted to the public role. 考虑执行以下部分活动。Consider performing some of the following activities.

  • 若要通过 Azure AD 登录名创建 Azure AD 用户,请参阅 CREATE USERTo create an Azure AD user from an Azure AD login, see CREATE USER.
  • 若要向数据库中的用户授予权限,请使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个内置数据库角色或自定义角色中,或者使用 GRANT 语句直接向用户授予权限 。To grant permissions to a user in a database, use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the built-in database roles or a custom role, or grant permissions to the user directly using the GRANT statement. 有关详细信息,请参阅非管理员角色其他服务器级管理角色ALTER SERVER ROLEGRANT 语句。For more information, see Non-administrator Roles, Additional server-level administrative roles, ALTER SERVER ROLE, and GRANT statement.
  • 若要授予服务器范围内的权限,请在 master 数据库中创建数据库用户,并使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个管理服务器角色。To grant server-wide permissions, create a database user in the master database and use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the administrative server roles. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLE服务器角色For more information, see Server-Level Roles and ALTER SERVER ROLE, and Server roles.
    • 使用以下命令将 sysadmin 角色添加到 Azure AD 登录名:ALTER SERVER ROLE sysadmin ADD MEMBER [AzureAD_Login_name]Use the following command to add the sysadmin role to an Azure AD login: ALTER SERVER ROLE sysadmin ADD MEMBER [AzureAD_Login_name]
  • 使用 GRANT 语句将服务器级别权限授予新的登录名或包含该登录名的角色 。Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. 有关详细信息,请参阅 GRANTFor more information, see GRANT.

限制Limitations

  • 不支持将映射到 Azure AD 组的 Azure AD 登录名设置为数据库所有者。Setting an Azure AD login mapped to an Azure AD group as the database owner is not supported.
  • 支持使用其他 Azure AD 主体模拟 Azure AD 服务器级别的主体,例如 EXECUTE AS 子句。Impersonation of Azure AD server-level principals using other Azure AD principals is supported, such as the EXECUTE AS clause.
  • 只有属于 sysadmin 角色的 SQL 服务器级主体(登录名)可以针对 Azure AD 主体执行以下操作:Only SQL server-level principals (logins) that are part of the sysadmin role can execute the following operations targeting Azure AD principals:
    • 作为用户执行EXECUTE AS USER
    • EXECUTE AS LOGINEXECUTE AS LOGIN
  • 从另一个 Azure AD 目录导入的外部(来宾)用户无法直接配置为托管实例的 Azure AD 管理员。External (guest) users imported from another Azure AD directory cannot be directly configured as an Azure AD admin for managed instance. 而是需将外部用户加入 Azure AD 启用安全机制的组,并将该组配置为实例管理员。Instead, join external user to an Azure AD security-enabled group and configure the group as the instance administrator.

示例Examples

A.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.B. 从 SID 创建登录名Creating a login from a SID

以下示例首先创建 SQL Server 身份验证登录,并确定该登录的 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

我的查询返回 0x241C11948AEEB749B0D22646DB1A19F2 作为 SID。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. 使用前面的查询中的 SID。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

C.C. 为本地 Azure AD 帐户创建登录名Creating a login for a local Azure AD account

以下示例为 Azure AD 帐户 joe@myaad.onmicrosoft.com 创建一个登录名,该帐户位于 myaad 的 Azure AD 中 。The following example creates a login for the Azure AD account joe@myaad.onmicrosoft.com that exists in the Azure AD of myaad.

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

D.D. 为联合 Azure AD 帐户创建登录Creating a login for a federated Azure AD account

以下示例为联合 Azure AD 帐户 bob@contoso.com 创建一个登录名,该帐户位于名为 contoso 的 Azure AD 中 。The following example creates a login for a federated Azure AD account bob@contoso.com that exists in the Azure AD called contoso. 用户 bob 也可以是来宾用户。User bob can also be a guest user.

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

E.E. 为 Azure AD 组创建登录Creating a login for an Azure AD group

以下示例为 Azure AD 组 mygroup 创建一个登录名,该组位于 myaad 的 Azure AD 中 The following example creates a login for the Azure AD group mygroup that exists in the Azure AD of myaad

CREATE LOGIN [mygroup] FROM EXTERNAL PROVIDER
GO

F.F. 为 Azure AD 应用程序创建登录名Creating a login for an Azure AD application

以下示例为 Azure AD 应用程序 myapp 创建一个登录名,该应用程序位于 myaad 的 Azure AD 中 The following example creates a login for the Azure AD application myapp that exists in the Azure AD of myaad

CREATE LOGIN [myapp] FROM EXTERNAL PROVIDER

G.G. 检查新添加的登录名Check newly added logins

要检查新添加的登录名,请执行以下 T-SQL 命令:To check the newly added login, execute the following T-SQL command:

SELECT *
FROM sys.server_principals;
GO

另请参阅See Also

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
*SQL 数据
仓库*
* SQL Data
Warehouse *
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

Azure SQL 数据仓库Azure SQL Data Warehouse

语法Syntax

-- Syntax for Azure SQL Data Warehouse
CREATE LOGIN login_name
 { WITH <option_list> }

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

参数Arguments

login_name 指定创建的登录名。login_name Specifies the name of the login that is created. Azure SQL 数据库仅支持 SQL 登录。Azure SQL Database supports only SQL logins.

PASSWORD =' password*' 指定正在创建的 SQL 登录名的密码。PASSWORD =' password*' Specifies the password for the SQL login that is being created. 请使用强密码。Use a strong password. 有关详细信息,请参阅强密码密码策略For more information, see Strong Passwords and Password Policy. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

密码是区分大小写的。Passwords are case-sensitive. 密码应始终至少包含八个字符,并且不能超过 128 个字符。Passwords should always be at least eight characters long, and cannot exceed 128 characters. 密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. 密码不能包含单引号或 login_name 。Passwords cannot contain single quotes, or the login_name.

SID = sid 用于重新创建登录名。SID = sid Used to recreate a login. 仅适用于 SQL Server 身份验证登录,不适用于 Windows 身份验证登录。Applies to SQL Server authentication logins only, not Windows authentication logins. 指定新 SQL Server 身份验证登录的 SID。Specifies the SID of the new SQL Server authentication login. 如果未使用此选项,SQL Server 将自动分配 SID。If this option is not used, SQL Server automatically assigns a SID. SID 结构取决于 SQL Server 版本。The SID structure depends on the SQL Server version. 对于 SQL 数据仓库,这是包含 0x01060000000000640000000000000000 的 32 字节 (binary(32)) 文本以及表示 GUID 的 16 个字节 。For SQL Data Warehouse, this is a 32 byte (binary(32)) literal consisting of 0x01060000000000640000000000000000 plus 16 bytes representing a GUID. 例如, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7For example, SID = 0x0106000000000064000000000000000014585E90117152449347750164BA00A7.

RemarksRemarks

登录名Logins

CREATE LOGIN 语句必须是批中的唯一语句 。The CREATE LOGIN statement must be the only statement in a batch.

在连接到 SQL 数据仓库的一些方法(如 sqlcmd)中,必须使用 <login>@<server> 符号将 SQL 数据仓库服务器名称追加到连接字符串中的登录名之后 。In some methods of connecting to SQL Data Warehouse, such as sqlcmd, you must append the SQL Data Warehouse server name to the login name in the connection string by using the <login>@<server> notation. 例如,如果登录为 login1,SQL 数据仓库服务器的完全限定名称是 servername.database.windows.net,则连接字符串的 username 参数应是 login1@servernameFor example, if your login is login1 and the fully qualified name of the SQL Data Warehouse server is servername.database.windows.net, the username parameter of the connection string should be login1@servername. 由于 username 参数的总长度为 128 个字符,因此,login_name 被限定为 127 个字符减去服务器名称的长度 。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. 在示例中,login_name 只能包含 117 个字符,因为 servername 包含 10 个字符。In the example, login_name can only be 117 characters long because servername is 10 characters.

在 SQL 数据仓库中,必须连接到 master 数据库才能创建登录。In SQL Data Warehouse, you must be connected to the master database to create a login.

SQL Server 规则允许你创建 <loginname>@<servername> 格式的 SQL Server 身份验证登录。SQL Server rules allow you create a SQL Server authentication login in the format <loginname>@<servername>. 如果你的 SQL 数据库SQL Database服务器是 myazureserver 并且登录名是 myemail@live.com,则必须提供 myemail@live.com@myazureserver 格式的登录名 。If your SQL 数据库SQL Database server is myazureserver and your login is **myemail@live.com**, then you must supply your login as **myemail@live.com@myazureserver**.

在 SQL 数据仓库中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL Data Warehouse, login data required to authenticate a connection and server-level firewall rules is temporarily cached in each database. 此缓存定期刷新。This cache is periodically refreshed. 若要强制刷新身份验证缓存并确保数据库具有最新版本的登录表,请执行 DBCC FLUSHAUTHCACHETo force a refresh of the authentication cache and make sure that a database has the latest version of the logins table, execute DBCC FLUSHAUTHCACHE.

有关 SQL 数据仓库登录的详细信息,请参阅管理 Azure SQL 数据库中的数据库和登录For more information about SQL Data Warehouse logins, see Managing Databases and Logins in Azure SQL Database.

权限Permissions

只有服务器级别主体登录(由预配过程创建)或 master 数据库中的 loginmanager 数据库角色成员可以创建新的登录。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. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLEFor more information, see Server-Level Roles and ALTER SERVER ROLE.

创建登录后After creating a login

创建登录后,该登录可以连接到 SQL 数据仓库,但是只具有授予 public 角色的权限 。After creating a login, the login can connect to SQL Data Warehouse 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. 有关详细信息,请参阅 CREATE USERFor more information, see CREATE USER.

  • 若要向数据库中的用户授予权限,请使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个内置数据库角色或自定义角色中,或者使用 GRANT 语句直接向用户授予权限 。To grant permissions to a user in a database, use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the built-in database roles or a custom role, or grant permissions to the user directly using the GRANT statement. 有关详细信息,请参阅非管理员角色其他服务器级管理角色ALTER SERVER ROLEGRANT 语句。For more information, see Non-administrator Roles, Additional server-level administrative roles, ALTER SERVER ROLE, and GRANT statement.

  • 若要授予服务器范围内的权限,请在 master 数据库中创建数据库用户,并使用 ALTER SERVER ROLE ... ADD MEMBER 语句将用户添加到其中一个管理服务器角色。To grant server-wide permissions, create a database user in the master database and use the ALTER SERVER ROLE ... ADD MEMBER statement to add the user to one of the administrative server roles. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLE服务器角色For more information, see Server-Level Roles and ALTER SERVER ROLE, and Server roles.

  • 使用 GRANT 语句将服务器级别权限授予新的登录名或包含该登录名的角色 。Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. 有关详细信息,请参阅 GRANTFor more information, see GRANT.

示例Examples

A.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.B. 从 SID 创建登录名Creating a login from a SID

以下示例首先创建 SQL Server 身份验证登录,并确定该登录的 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

我的查询返回 0x241C11948AEEB749B0D22646DB1A19F2 作为 SID。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. 使用前面的查询中的 SID。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

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
* Analytics
Platform System (PDW) *
* Analytics
Platform System (PDW) *

 

分析平台系统Analytics Platform System

语法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 指定创建的登录名。login_name Specifies the name of the login that is created. 有四种类型的登录名:SQL Server 登录名、Windows 登录名、证书映射登录名以及非对称密钥映射登录名。There are four types of logins: SQL Server logins, Windows logins, certificate-mapped logins, and asymmetric key-mapped logins. 在创建从 Windows 域帐户映射的登录名时,必须以 [<domainName>\<login_name>] 格式使用 Windows 2000 之前的用户登录名。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>]. 不能使用 login_name@DomainName 格式的 UPN。You cannot use a UPN in the format login_name@DomainName. 有关示例,请参阅本文后面的示例 D。For an example, see example D later in this article. 身份验证登录的类型为 sysname,它必须符合标识符规则,且不能包含“\” 。Authentication logins are type sysname and must conform to the rules for Identifiers and cannot contain a '\'. Windows 登录名可以包含“\” 。Windows logins can contain a '\'. 基于 Active Directory 用户的登录名的名称限制为少于 21 个字符。Logins based on Active Directory users, are limited to names of fewer than 21 characters.

PASSWORD ='password' 仅适用于 SQL Server 登录名 。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. 从 SQL Server 2012 (11.x) 开始,存储的密码信息使用 SHA-512 加盐密码进行计算。Beginning with SQL Server 2012 (11.x), stored password information is calculated using SHA-512 of the salted password.

密码是区分大小写的。Passwords are case-sensitive. 密码应始终至少包含八个字符,并且不能超过 128 个字符。Passwords should always be at least eight characters long, and cannot exceed 128 characters. 密码可以包含 a-z、A-Z、0-9 和大多数非字母数字字符。Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. 密码不能包含单引号或 login_name 。Passwords cannot contain single quotes, or the login_name.

MUST_CHANGE 仅适用于 SQL Server 登录。MUST_CHANGE Applies to SQL Server logins only. 如果包括此选项,则 SQL Server 将在首次使用新登录时提示用户输入新密码。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 } 仅适用于 SQL Server 登录名。CHECK_EXPIRATION = { ON | OFF } Applies to SQL Server logins only. 指定是否应对此登录帐户强制实施密码过期策略。Specifies whether password expiration policy should be enforced on this login. 默认值为 OFF。The default value is OFF.

CHECK_POLICY = { ON | OFF } 仅适用于 SQL Server 登录名。CHECK_POLICY = { ON | OFF } Applies to SQL Server logins only. 指定应对此登录强制实施运行 SQL Server 的计算机的 Windows 密码策略。Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. 默认值为 ON。The default value is ON.

如果 Windows 策略要求强密码,密码必须至少包含以下四个特点中的三个:If the Windows policy requires strong passwords, passwords must contain at least three of the following four characteristics:

  • 大写字符 (A-Z)。An uppercase character (A-Z).
  • 小写字符 (a-z)。A lowercase character (a-z).
  • 数字 (0-9)。A digit (0-9).
  • 一个非字母数字字符,如空格、_、@、*、^、%、!、$、# 或 &。One of the non-alphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.

WINDOWS 指定将登录名映射到 Windows 登录名。WINDOWS Specifies that the login be mapped to a Windows login.

RemarksRemarks

  • 密码是区分大小写的。Passwords are case-sensitive.
  • 如果指定 MUST_CHANGE,则 CHECK_EXPIRATION 和 CHECK_POLICY 必须设置为 ON。If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. 否则,该语句将失败。Otherwise, the statement will fail.
  • 不支持 CHECK_POLICY = OFF 和 CHECK_EXPIRATION = ON 的组合。A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.
  • 如果 CHECK_POLICY 设置为 OFF,将对 lockout_time 进行重置,并将 CHECK_EXPIRATION 设置为 OFF 。When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.

重要

只有在 Windows Server 2003 及更高版本上才会强制执行 CHECK_EXPIRATION 和 CHECK_POLICY。CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later. 有关详细信息,请参阅 Password PolicyFor more information, see Password Policy.

权限Permissions

只有具有针对服务器的 ALTER ANY LOGIN 权限或 securityadmin 固定服务器角色的成员身份的用户才可创建登录 。Only users with ALTER ANY LOGIN permission on the server or membership in the securityadmin fixed server role can create logins. 有关详细信息,请参阅服务器级别角色ALTER SERVER ROLEFor more information, see Server-Level Roles and ALTER SERVER ROLE.

创建登录后After creating a login

创建登录后,该登录可以连接到 SQL 数据仓库,但是只具有授予 public 角色的权限 。After creating a login, the login can connect to SQL Data Warehouse, 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. 有关详细信息,请参阅 CREATE USERFor more information, see CREATE USER.
  • 使用 CREATE SERVER ROLE 创建用户定义的服务器角色。Create a user-defined server role by using CREATE SERVER ROLE. 使用 ALTER SERVER ROLE ... ADD MEMBER 将新登录名添加到用户定义的服务器角色 。Use ALTER SERVER ROLE ... ADD MEMBER to add the new login to the user-defined server role. 有关详细信息,请参阅 CREATE SERVER ROLEALTER SERVER ROLEFor more information, see CREATE SERVER ROLE and ALTER SERVER ROLE.
  • 使用 sp_addsrvrolemember 将登录名添加到固定服务器角色 。Use sp_addsrvrolemember to add the login to a fixed server role. 有关详细信息,请参阅服务器级别角色sp_addsrvrolememberFor more information, see Server-Level Roles and sp_addsrvrolemember.
  • 使用 GRANT 语句将服务器级别权限授予新的登录名或包含该登录名的角色 。Use the GRANT statement, to grant server-level permissions to the new login or to a role containing the login. 有关详细信息,请参阅 GRANTFor more information, see GRANT.

示例Examples

G.G. 创建具有密码的 SQL Server 身份验证登录名Creating a SQL Server authentication login with a password

下面的示例创建密码为 A2c3456 的登录名 Mary7The following example creates the login Mary7 with password A2c3456.

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

H.H. 使用选项Using Options

下面的示例创建具有密码和一些可选参数的登录名 Mary8The 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.I. 从 Windows 域帐户创建登录名Creating a login from a Windows domain account

下面的示例在 Contoso 域中使用 Windows 域帐户创建名为 Mary 的登录名。The following example creates a login from a Windows domain account named Mary in the Contoso domain.

CREATE LOGIN [Contoso\Mary] FROM WINDOWS;
GO

另请参阅See Also