ALTER LOGIN (Transact-SQL)ALTER LOGIN (Transact-SQL)

更改 SQL ServerSQL Server 登录帐户的属性。Changes the properties of a SQL ServerSQL Server login account.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon 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

ALTER LOGIN login_name
    {
    <status_option>
    | WITH <set_option> [ ,... ]
    | <cryptographic_credential_option>
    }
[;]

<status_option> ::=
        ENABLE | DISABLE

<set_option> ::=
    PASSWORD = 'password' | hashed_password HASHED
    [
      OLD_PASSWORD = 'oldpassword'
      | <password_option> [<password_option> ]
    ]
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }
    | CREDENTIAL = credential_name
    | NO CREDENTIAL

<password_option> ::=
    MUST_CHANGE | UNLOCK

<cryptographic_credentials_option> ::=
    ADD CREDENTIAL credential_name
  | DROP CREDENTIAL credential_name

参数Arguments

login_name 指定正在更改的 SQL ServerSQL Server 登录名的名称。login_name Specifies the name of the SQL ServerSQL Server login that is being changed. 域登录名必须用方括号括起来,其格式为 [domain\user]。Domain logins must be enclosed in brackets in the format [domain\user].

ENABLE | DISABLE 启用或禁用此登录名。ENABLE | DISABLE Enables or disables this login. 禁用登录名不会影响已连接登录名的行为。Disabling a login does not affect the behavior of logins that are already connected. (使用 KILL 语句终止现有连接。)禁用的登录名将保留它们的权限,且仍然可以模拟。(Use the KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.

PASSWORD =' password ' 仅适用于 SQL ServerSQL Server 登录名。PASSWORD ='password' Applies only to SQL ServerSQL Server logins. 指定正在更改的登录名的密码。Specifies the password for the login that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

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.

重要

当一个登录名(或包含的数据库用户)进行连接并经过身份验证后,该连接缓存有关该登录名的标识信息。When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. 对于 Windows 身份验证登录,此标识信息包含有关 Windows 组中的成员身份的信息。For a Windows Authentication login, this includes information about membership in Windows groups. 只要保持连接,该登录名的标识就保持已经过身份验证的状态。The identity of the login remains authenticated as long as the connection is maintained. 若要在标识中强制进行更改(例如,重置密码或更改 Windows 组成员身份),则必须从身份验证机构(Windows 或 SQL ServerSQL Server)注销,然后再次登录。To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL ServerSQL Server), and log in again. sysadmin 固定服务器角色的成员或任何使用 ALTER ANY CONNECTION 权限的登录名都可以使用 KILL 命令结束连接,并强制登录名重新连接。A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management StudioSQL Server Management Studio 可以在打开与对象资源管理器和查询编辑器窗口之间的多个连接时重用连接信息。can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. 关闭所有连接可强制重新连接。Close all connections to force reconnection.

HASHED 仅适用于 SQL ServerSQL Server 登录名。HASHED Applies to SQL ServerSQL Server logins only. 指定在 PASSWORD 参数后输入的密码已经过哈希运算。Specifies that the password entered after the PASSWORD argument is already hashed. 如果未选择此选项,则在将密码存储到数据库之前,对其进行哈希运算。If this option is not selected, the password is hashed before being stored in the database. 此选项只能用于在两台服务器之间同步登录名。This option should only be used for login synchronization between two servers. 切勿使用 HASHED 选项定期更改密码。Do not use the HASHED option to routinely change passwords.

OLD_PASSWORD =' oldpassword ' 仅适用于 SQL ServerSQL Server 登录名。OLD_PASSWORD ='oldpassword' Applies only to SQL ServerSQL Server logins. 要指派新密码的登录的当前密码。The current password of the login to which a new password will be assigned. 密码是区分大小写的。Passwords are case-sensitive.

MUST_CHANGE 仅适用于 SQL ServerSQL Server 登录名。MUST_CHANGE Applies only to SQL ServerSQL Server logins. 如果包括此选项,则 SQL ServerSQL Server 将在首次使用已更改的登录名时提示输入更新的密码。If this option is included, SQL ServerSQL Server will prompt for an updated password the first time the altered login is used.

DEFAULT_DATABASE = database 指定将指派给登录名的默认数据库。DEFAULT_DATABASE =database Specifies a default database to be assigned to the login.

DEFAULT_LANGUAGE = language 指定将指派给登录名的默认语言。DEFAULT_LANGUAGE =language Specifies a default language to be assigned to the login. 所有 SQL 数据库登录名的默认语言为英语,并且无法更改。The default language for all SQL Database logins is English and cannot be changed. Linux 上 SQL ServerSQL Serversa 登录名的默认语言是英语,但可以更改。The default language of the sa login on SQL ServerSQL Server on Linux, is English but it can be changed.

NAME = login_name 正在重命名的登录的新名称。NAME = login_name The new name of the login that is being renamed. 如果是 Windows 登录,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL ServerSQL Server 中的登录相关联的 SID。If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL ServerSQL Server. SQL ServerSQL Server 登录名的新名称不能包含反斜杠字符 (\)。The new name of a SQL ServerSQL Server login cannot contain a backslash character (\).

CHECK_EXPIRATION = { ON | OFF } 仅适用于 SQL ServerSQL Server 登录名。CHECK_EXPIRATION = { ON | OFF } Applies only to SQL ServerSQL Server logins. 指定是否应对此登录帐户强制实施密码过期策略。Specifies whether password expiration policy should be enforced on this login. 默认值为 OFF。The default value is OFF.

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

CREDENTIAL = credential_name 将映射到 SQL ServerSQL Server 登录的凭据的名称。CREDENTIAL = credential_name The name of a credential to be mapped to a SQL ServerSQL Server login. 该凭据必须已存在于服务器中。The credential must already exist in the server. 有关详细信息,请参阅凭据For more information, see Credentials. 凭据不能映射到 sa 登录名。A credential cannot be mapped to the sa login.

NO CREDENTIAL 删除登录到服务器凭据的当前所有映射。NO CREDENTIAL Removes any existing mapping of the login to a server credential. 有关详细信息,请参阅凭据For more information, see Credentials.

UNLOCK 仅适用于 SQL ServerSQL Server 登录名。UNLOCK Applies only to SQL ServerSQL Server logins. 指定应解锁被锁定的登录名。Specifies that a login that is locked out should be unlocked.

ADD CREDENTIAL 将可扩展的密钥管理 (EKM) 提供程序凭据添加到登录名。ADD CREDENTIAL Adds an Extensible Key Management (EKM) provider credential to the login. 有关详细信息,请参阅可扩展的密钥管理 (EKM)For more information, see Extensible Key Management (EKM).

DROP CREDENTIAL 从登录名删除可扩展密钥管理 (EKM) 提供程序凭据。DROP CREDENTIAL Removes an Extensible Key Management (EKM) provider credential from the login. 有关详细信息,请参阅 [可扩展的密钥管理 (EKM)] (../..For more information, see [Extensible Key Management (EKM)] (../.. /relational-databases/security/encryption/extensible-key-management-ekm.md)。/relational-databases/security/encryption/extensible-key-management-ekm.md).

RemarksRemarks

如果 CHECK_POLICY 设置为 ON,则无法使用 HASHED 参数。When CHECK_POLICY is set to ON, the HASHED argument cannot be used.

如果 CHECK_POLICY 更改为 ON,则将出现以下行为:When CHECK_POLICY is changed to ON, the following behavior occurs:

  • 用当前的密码哈希值初始化密码历史记录。The password history is initialized with the value of the current password hash.

    如果 CHECK_POLICY 更改为 OFF,则将出现以下行为:When CHECK_POLICY is changed to OFF, the following behavior occurs:

  • CHECK_EXPIRATION 也设置为 OFF。CHECK_EXPIRATION is also set to OFF.

  • 清除密码历史记录。The password history is cleared.

  • 重置 lockout_time 的值 。The value of lockout_time is reset.

如果指定 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。If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. 包含此选项组合的 ALTER LOGIN 语句将失败。An ALTER LOGIN statement that has this combination of options will fail.

不能使用带 DISABLE 参数的 ALTER_LOGIN 来拒绝对 Windows 组的访问。You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. 例如,ALTER_LOGIN [domain\group] DISABLE 将返回下列错误信息 :For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:

`"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login '*Domain\Group*', because it does not exist or you do not have permission."`

This is by design.

SQL 数据库SQL Database中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL 数据库SQL Database, login data required to authenticate a connection and server-level firewall rules are 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.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

如果使用 CREDENTIAL 选项,则还需要 ALTER ANY CREDENTIAL 权限。If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限 :If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • 在不提供旧密码的情况下重置密码。Resetting the password without supplying the old password.
  • 启用 MUST_CHANGE、CHECK_POLICY 或 CHECK_EXPIRATION。Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.
  • 更改登录名。Changing the login name.
  • 启用或禁用登录名。Enabling or disabling the login.
  • 将登录名映射到其他凭据。Mapping the login to a different credential.

主体可更改用于自身登录的密码、默认语言以及默认数据库。A principal can change the password, default language, and default database for its own login.

示例Examples

A.A. 启用已禁用的登录名Enabling a disabled login

以下示例将启用 Mary5 登录名。The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B.B. 更改登录密码Changing the password of a login

以下示例将登录名 Mary5 的密码更改为强密码。The following example changes the password of login Mary5 to a strong password.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C.C. 登录时更改登录密码Changing the password of a login when logged in as the login

如果正在尝试更改当前登录所用登录名的密码,但没有 ALTER ANY LOGIN 权限,则必须指定 OLD_PASSWORD 选项。If you are attempting to change the password of the login that you're currently logged in with and you do not have the ALTER ANY LOGIN permission you must specify the OLD_PASSWORD option.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>' OLD_PASSWORD = '<oldWeakPasswordHere>';

D.D. 更改登录名称Changing the name of a login

以下示例将 Mary5 登录名称更改为 John2The following example changes the name of login Mary5 to John2.

ALTER LOGIN Mary5 WITH NAME = John2;

E.E. 将登录名映射到凭据Mapping a login to a credential

以下示例将登录名 John2 映射到凭据 Custodian04The following example maps the login John2 to the credential Custodian04.

ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;

F.F. 将登录名映射到可扩展密钥管理凭据Mapping a login to an Extensible Key Management credential

以下示例将登录名 Mary5 映射到 EKM 凭据 EKMProvider1The following example maps the login Mary5 to the EKM credential EKMProvider1.

ALTER LOGIN Mary5
ADD CREDENTIAL EKMProvider1;
GO

F.F. 解除锁定登录名Unlocking a login

若要解除锁定 SQL ServerSQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。To unlock a SQL ServerSQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;

GO

若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO

G.G. 使用 HASHED 更改登录名的密码Changing the password of a login using HASHED

以下示例将 TestUser 登录名的密码更改为已经过哈希运算的值。The following example changes the password of the TestUser login to an already hashed value.

ALTER LOGIN TestUser WITH
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;
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

SQL ServerSQL Server

语法Syntax

-- Syntax for Azure SQL Database and Azure SQL Data Warehouse

ALTER LOGIN login_name
  {
      <status_option>
    | WITH <set_option> [ ,.. .n ]
  }
[;]

<status_option> ::=
    ENABLE | DISABLE

<set_option> ::=
    PASSWORD ='password'
    [
      OLD_PASSWORD ='oldpassword'
    ]
    | NAME = login_name

参数Arguments

login_name 指定正在更改的 SQL ServerSQL Server 登录名的名称。login_name Specifies the name of the SQL ServerSQL Server login that is being changed. 域登录名必须用方括号括起来,其格式为 [domain\user]。Domain logins must be enclosed in brackets in the format [domain\user].

ENABLE | DISABLE 启用或禁用此登录名。ENABLE | DISABLE Enables or disables this login. 禁用登录名不会影响已连接登录名的行为。Disabling a login does not affect the behavior of logins that are already connected. (使用 KILL 语句终止现有连接。)禁用的登录名将保留它们的权限,且仍然可以模拟。(Use the KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.

PASSWORD =' password ' 仅适用于 SQL ServerSQL Server 登录名。PASSWORD ='password' Applies only to SQL ServerSQL Server logins. 指定正在更改的登录名的密码。Specifies the password for the login that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

与 SQL 数据库持续保持活动连接需要至少每隔 10 小时进行重新授权(由数据库引擎执行)。Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. 数据库引擎使用最初提交的密码尝试重新授权,且无需用户输入。The Database Engine attempts reauthorization using the originally submitted password and no user input is required. 出于性能原因,在 SQL 数据库中重置密码时,连接将不会重新进行身份验证,即使该连接因连接池而重置。For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. 这与本地 SQL Server 的行为不同。This is different from the behavior of on-premises SQL Server. 如果自最初授权连接时已更改密码,则必须终止连接,并使用新密码建立新连接。If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. 具有 KILL DATABASE CONNECTION 权限的用户可使用 KILL 命令,显式终止与 SQL 数据库的连接。A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. 有关详细信息,请参阅 KILLFor more information, see KILL.

重要

当一个登录名(或包含的数据库用户)进行连接并经过身份验证后,该连接缓存有关该登录名的标识信息。When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. 对于 Windows 身份验证登录,此标识信息包含有关 Windows 组中的成员身份的信息。For a Windows Authentication login, this includes information about membership in Windows groups. 只要保持连接,该登录名的标识就保持已经过身份验证的状态。The identity of the login remains authenticated as long as the connection is maintained. 若要在标识中强制进行更改(例如,重置密码或更改 Windows 组成员身份),则必须从身份验证机构(Windows 或 SQL ServerSQL Server)注销,然后再次登录。To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL ServerSQL Server), and log in again. sysadmin 固定服务器角色的成员或任何使用 ALTER ANY CONNECTION 权限的登录名都可以使用 KILL 命令结束连接,并强制登录名重新连接。A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management StudioSQL Server Management Studio 可以在打开与对象资源管理器和查询编辑器窗口之间的多个连接时重用连接信息。can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. 关闭所有连接可强制重新连接。Close all connections to force reconnection.

OLD_PASSWORD =' oldpassword ' 仅适用于 SQL ServerSQL Server 登录名。OLD_PASSWORD ='oldpassword' Applies only to SQL ServerSQL Server logins. 要指派新密码的登录的当前密码。The current password of the login to which a new password will be assigned. 密码是区分大小写的。Passwords are case-sensitive.

NAME = login_name 正在重命名的登录的新名称。NAME = login_name The new name of the login that is being renamed. 如果是 Windows 登录,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL ServerSQL Server 中的登录相关联的 SID。If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL ServerSQL Server. SQL ServerSQL Server 登录名的新名称不能包含反斜杠字符 (\)。The new name of a SQL ServerSQL Server login cannot contain a backslash character (\).

RemarksRemarks

SQL 数据库SQL Database中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL 数据库SQL Database, login data required to authenticate a connection and server-level firewall rules are 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.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限 :If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • 在不提供旧密码的情况下重置密码。Resetting the password without supplying the old password.
  • 更改登录名。Changing the login name.
  • 启用或禁用登录名。Enabling or disabling the login.
  • 将登录名映射到其他凭据。Mapping the login to a different credential.

主体可更改自己的登录密码。A principal can change the password for its own login.

示例Examples

这些示例还包括使用其他 SQL 产品的示例。These examples also include examples for using other SQL products. 请参阅上述支持的参数。Please see which arguments are supported above.

A.A. 启用已禁用的登录名Enabling a disabled login

以下示例将启用 Mary5 登录名。The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B.B. 更改登录密码Changing the password of a login

以下示例将登录名 Mary5 的密码更改为强密码。The following example changes the password of login Mary5 to a strong password.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C.C. 更改登录名称Changing the name of a login

以下示例将 Mary5 登录名称更改为 John2The following example changes the name of login Mary5 to John2.

ALTER LOGIN Mary5 WITH NAME = John2;

D.D. 将登录名映射到凭据Mapping a login to a credential

以下示例将登录名 John2 映射到凭据 Custodian04The following example maps the login John2 to the credential Custodian04.

ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;

E.E. 将登录名映射到可扩展密钥管理凭据Mapping a login to an Extensible Key Management credential

以下示例将登录名 Mary5 映射到 EKM 凭据 EKMProvider1The following example maps the login Mary5 to the EKM credential EKMProvider1.

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

ALTER LOGIN Mary5
ADD CREDENTIAL EKMProvider1;
GO

F.F. 解除锁定登录名Unlocking a login

若要解除锁定 SQL ServerSQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。To unlock a SQL ServerSQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;

GO

若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO

G.G. 使用 HASHED 更改登录名的密码Changing the password of a login using HASHED

以下示例将 TestUser 登录名的密码更改为已经过哈希运算的值。The following example changes the password of the TestUser login to an already hashed value.

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

ALTER LOGIN TestUser WITH
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;
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 SQL Server and Azure SQL Database managed instance

ALTER LOGIN login_name
    {
    <status_option>
    | WITH <set_option> [ ,... ]
    | <cryptographic_credential_option>
    }
[;]

<status_option> ::=
        ENABLE | DISABLE

<set_option> ::=
    PASSWORD = 'password' | hashed_password HASHED
    [
      OLD_PASSWORD = 'oldpassword'
      | <password_option> [<password_option> ]
    ]
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }
    | CREDENTIAL = credential_name
    | NO CREDENTIAL

<password_option> ::=
    MUST_CHANGE | UNLOCK

<cryptographic_credentials_option> ::=
    ADD CREDENTIAL credential_name
  | DROP CREDENTIAL credential_name

重要

SQL 数据库托管实例的 Azure AD 登录名当前为公共预览版 。Azure AD logins for SQL Database managed instance is in public preview.

-- Syntax for Azure SQL Database managed instance using Azure AD logins

ALTER LOGIN login_name
  {
      <status_option>
    | WITH <set_option> [ ,.. .n ]
  }
[;]

<status_option> ::=
    ENABLE | DISABLE

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

参数Arguments

适用于 SQL 和 Azure AD 登录名的参数Arguments applicable to SQL and Azure AD logins

login_name 指定正在更改的 SQL ServerSQL Server 登录名的名称。login_name Specifies the name of the SQL ServerSQL Server login that is being changed. Azure AD 登录名必须以 user@domain 的形式指定。Azure AD logins must be specified as user@domain. 例如,john.smith@contoso.com,或者指定为 Azure AD 组或应用程序名称。For example, john.smith@contoso.com, or as the Azure AD group or application name. 对于 Azure AD 登录名,login_name 必须与 master 数据库中创建的现有 Azure AD 登录名对应 。For Azure AD logins, the login_name must correspond to an existing Azure AD login created in the master database.

ENABLE | DISABLE 启用或禁用此登录名。ENABLE | DISABLE Enables or disables this login. 禁用登录名不会影响已连接登录名的行为。Disabling a login does not affect the behavior of logins that are already connected. (使用 KILL 语句终止现有连接。)禁用的登录名将保留它们的权限,且仍然可以模拟。(Use the KILL statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.

DEFAULT_DATABASE = database 指定将指派给登录名的默认数据库。DEFAULT_DATABASE =database Specifies a default database to be assigned to the login.

DEFAULT_LANGUAGE = language 指定将指派给登录名的默认语言。DEFAULT_LANGUAGE =language Specifies a default language to be assigned to the login. 所有 SQL 数据库登录名的默认语言为英语,并且无法更改。The default language for all SQL Database logins is English and cannot be changed. Linux 上 SQL ServerSQL Serversa 登录名的默认语言是英语,但可以更改。The default language of the sa login on SQL ServerSQL Server on Linux, is English but it can be changed.

仅适用于 SQL 登录名的参数Arguments applicable only to SQL logins

PASSWORD =' password ' 仅适用于 SQL ServerSQL Server 登录名。PASSWORD ='password' Applies only to SQL ServerSQL Server logins. 指定正在更改的登录名的密码。Specifies the password for the login that is being changed. 密码是区分大小写的。Passwords are case-sensitive. 使用外部登录名(如 Azure AD 登录名)时,密码也不适用。Passwords also do not apply when used with external logins, like Azure AD logins.

与 SQL 数据库持续保持活动连接需要至少每隔 10 小时进行重新授权(由数据库引擎执行)。Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. 数据库引擎使用最初提交的密码尝试重新授权,且无需用户输入。The Database Engine attempts reauthorization using the originally submitted password and no user input is required. 出于性能原因,在 SQL 数据库中重置密码时,连接将不会重新进行身份验证,即使该连接因连接池而重置。For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. 这与本地 SQL Server 的行为不同。This is different from the behavior of on-premises SQL Server. 如果自最初授权连接时已更改密码,则必须终止连接,并使用新密码建立新连接。If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. 具有 KILL DATABASE CONNECTION 权限的用户可使用 KILL 命令,显式终止与 SQL 数据库的连接。A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. 有关详细信息,请参阅 KILLFor more information, see KILL.

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 ServerSQL Server 登录名。HASHED Applies to SQL ServerSQL Server logins only. 指定在 PASSWORD 参数后输入的密码已经过哈希运算。Specifies that the password entered after the PASSWORD argument is already hashed. 如果未选择此选项,则在将密码存储到数据库之前,对其进行哈希运算。If this option is not selected, the password is hashed before being stored in the database. 此选项只能用于在两台服务器之间同步登录名。This option should only be used for login synchronization between two servers. 切勿使用 HASHED 选项定期更改密码。Do not use the HASHED option to routinely change passwords.

OLD_PASSWORD =' oldpassword ' 仅适用于 SQL ServerSQL Server 登录名。OLD_PASSWORD ='oldpassword' Applies only to SQL ServerSQL Server logins. 要指派新密码的登录的当前密码。The current password of the login to which a new password will be assigned. 密码是区分大小写的。Passwords are case-sensitive.

MUST_CHANGEMUST_CHANGE
仅适用于 SQL ServerSQL Server 登录名。Applies only to SQL ServerSQL Server logins. 如果包括此选项,则 SQL ServerSQL Server 将在首次使用已更改的登录名时提示输入更新的密码。If this option is included, SQL ServerSQL Server will prompt for an updated password the first time the altered login is used.

NAME = login_name 正在重命名的登录的新名称。NAME = login_name The new name of the login that is being renamed. 如果使用 Windows 登录名,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL ServerSQL Server 中的登录名关联的 SID。If the login is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL ServerSQL Server. SQL ServerSQL Server 登录名的新名称不能包含反斜杠字符 (\)。The new name of a SQL ServerSQL Server login cannot contain a backslash character (\).

CHECK_EXPIRATION = { ON | OFF } 仅适用于 SQL ServerSQL Server 登录名。CHECK_EXPIRATION = { ON | OFF } Applies only to SQL ServerSQL Server logins. 指定是否应对此登录帐户强制实施密码过期策略。Specifies whether password expiration policy should be enforced on this login. 默认值为 OFF。The default value is OFF.

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

CREDENTIAL = credential_name 将映射到 SQL ServerSQL Server 登录的凭据的名称。CREDENTIAL = credential_name The name of a credential to be mapped to a SQL ServerSQL Server login. 该凭据必须已存在于服务器中。The credential must already exist in the server. 有关详细信息,请参阅凭据For more information, see Credentials. 凭据不能映射到 sa 登录名。A credential cannot be mapped to the sa login.

NO CREDENTIAL 删除登录到服务器凭据的当前所有映射。NO CREDENTIAL Removes any existing mapping of the login to a server credential. 有关详细信息,请参阅凭据For more information, see Credentials.

UNLOCK 仅适用于 SQL ServerSQL Server 登录名。UNLOCK Applies only to SQL ServerSQL Server logins. 指定应解锁被锁定的登录名。Specifies that a login that is locked out should be unlocked.

ADD CREDENTIAL 将可扩展的密钥管理 (EKM) 提供程序凭据添加到登录名。ADD CREDENTIAL Adds an Extensible Key Management (EKM) provider credential to the login. 有关详细信息,请参阅可扩展的密钥管理 (EKM)For more information, see Extensible Key Management (EKM).

DROP CREDENTIAL 从登录名删除可扩展密钥管理 (EKM) 提供程序凭据。DROP CREDENTIAL Removes an Extensible Key Management (EKM) provider credential from the login. 有关详细信息,请参阅可扩展的密钥管理 (EKM)For more information, see Extensible Key Management (EKM).

RemarksRemarks

如果 CHECK_POLICY 设置为 ON,则无法使用 HASHED 参数。When CHECK_POLICY is set to ON, the HASHED argument cannot be used.

如果 CHECK_POLICY 更改为 ON,则将出现以下行为:When CHECK_POLICY is changed to ON, the following behavior occurs:

  • 用当前的密码哈希值初始化密码历史记录。The password history is initialized with the value of the current password hash.

    如果 CHECK_POLICY 更改为 OFF,则将出现以下行为:When CHECK_POLICY is changed to OFF, the following behavior occurs:

  • CHECK_EXPIRATION 也设置为 OFF。CHECK_EXPIRATION is also set to OFF.

  • 清除密码历史记录。The password history is cleared.

  • 重置 lockout_time 的值 。The value of lockout_time is reset.

如果指定 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。If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. 包含此选项组合的 ALTER LOGIN 语句将失败。An ALTER LOGIN statement that has this combination of options will fail.

不能使用带 DISABLE 参数的 ALTER_LOGIN 来拒绝对 Windows 组的访问。You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. 这是设计的结果。This is by design. 例如,ALTER_LOGIN [domain\group] DISABLE 将返回下列错误信息 :For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:

`"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login '*Domain\Group*', because it does not exist or you do not have permission."`

SQL 数据库SQL Database中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL 数据库SQL Database, login data required to authenticate a connection and server-level firewall rules are 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.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

如果使用 CREDENTIAL 选项,则还需要 ALTER ANY CREDENTIAL 权限。If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限 :If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • 在不提供旧密码的情况下重置密码。Resetting the password without supplying the old password.
  • 启用 MUST_CHANGE、CHECK_POLICY 或 CHECK_EXPIRATION。Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.
  • 更改登录名。Changing the login name.
  • 启用或禁用登录名。Enabling or disabling the login.
  • 将登录名映射到其他凭据。Mapping the login to a different credential.

主体可更改用于自身登录的密码、默认语言以及默认数据库。A principal can change the password, default language, and default database for its own login.

只有拥有 sysadmin 权限的 SQL 主体才能对 Azure AD 登录名执行 ALTER LOGIN 命令。Only a SQL principal with sysadmin privileges can execute an ALTER LOGIN command against an Azure AD login.

示例Examples

这些示例还包括使用其他 SQL 产品的示例。These examples also include examples for using other SQL products. 请参阅上述支持的参数。Please see which arguments are supported above.

A.A. 启用已禁用的登录名Enabling a disabled login

以下示例将启用 Mary5 登录名。The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B.B. 更改登录密码Changing the password of a login

以下示例将登录名 Mary5 的密码更改为强密码。The following example changes the password of login Mary5 to a strong password.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C.C. 更改登录名称Changing the name of a login

以下示例将 Mary5 登录名称更改为 John2The following example changes the name of login Mary5 to John2.

ALTER LOGIN Mary5 WITH NAME = John2;

D.D. 将登录名映射到凭据Mapping a login to a credential

以下示例将登录名 John2 映射到凭据 Custodian04The following example maps the login John2 to the credential Custodian04.

ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;

E.E. 将登录名映射到可扩展密钥管理凭据Mapping a login to an Extensible Key Management credential

以下示例将登录名 Mary5 映射到 EKM 凭据 EKMProvider1The following example maps the login Mary5 to the EKM credential EKMProvider1.

适用范围 :SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017,以及 Azure SQL 数据库托管实例。Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, and Azure SQL Database managed instance.

ALTER LOGIN Mary5
ADD CREDENTIAL EKMProvider1;
GO

F.F. 解除锁定登录名Unlocking a login

若要解除锁定 SQL ServerSQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。To unlock a SQL ServerSQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;

GO

若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO

G.G. 使用 HASHED 更改登录名的密码Changing the password of a login using HASHED

以下示例将 TestUser 登录名的密码更改为已经过哈希运算的值。The following example changes the password of the TestUser login to an already hashed value.

适用范围 :SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017,以及 Azure SQL 数据库托管实例。Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, and Azure SQL Database managed instance.

ALTER LOGIN TestUser WITH
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;
GO

H.H. 禁用 Azure AD 用户的登录名Disabling the login of an Azure AD user

以下示例禁用 Azure AD 用户 joe@contoso.com 的登录。The following example disables the login of an Azure AD user, joe@contoso.com.

ALTER LOGIN [joe@contoso.com] DISABLE

另请参阅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 Database and Azure SQL Data Warehouse

ALTER LOGIN login_name
  {
      <status_option>
    | WITH <set_option> [ ,.. .n ]
  }
[;]

<status_option> ::=
    ENABLE | DISABLE

<set_option> ::=
    PASSWORD ='password'
    [
      OLD_PASSWORD ='oldpassword'
    ]
    | NAME = login_name

参数Arguments

login_name 指定正在更改的 SQL ServerSQL Server 登录名的名称。login_name Specifies the name of the SQL ServerSQL Server login that is being changed. 域登录名必须用方括号括起来,其格式为 [domain\user]。Domain logins must be enclosed in brackets in the format [domain\user].

ENABLE | DISABLE 启用或禁用此登录名。ENABLE | DISABLE Enables or disables this login. 禁用登录名不会影响已连接登录名的行为。Disabling a login does not affect the behavior of logins that are already connected. (使用 KILL 语句终止现有连接。)禁用的登录名将保留它们的权限,且仍然可以模拟。(Use the KILL statement to terminate an existing connections.) Disabled logins retain their permissions and can still be impersonated.

PASSWORD =' password ' 仅适用于 SQL ServerSQL Server 登录名。PASSWORD ='password' Applies only to SQL ServerSQL Server logins. 指定正在更改的登录名的密码。Specifies the password for the login that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

与 SQL 数据库持续保持活动连接需要至少每隔 10 小时进行重新授权(由数据库引擎执行)。Continuously active connections to SQL Database require reauthorization (performed by the Database Engine) at least every 10 hours. 数据库引擎使用最初提交的密码尝试重新授权,且无需用户输入。The Database Engine attempts reauthorization using the originally submitted password and no user input is required. 出于性能原因,在 SQL 数据库中重置密码时,连接将不会重新进行身份验证,即使该连接因连接池而重置。For performance reasons, when a password is reset in SQL Database, the connection will not be re-authenticated, even if the connection is reset due to connection pooling. 这与本地 SQL Server 的行为不同。This is different from the behavior of on-premises SQL Server. 如果自最初授权连接时已更改密码,则必须终止连接,并使用新密码建立新连接。If the password has been changed since the connection was initially authorized, the connection must be terminated and a new connection made using the new password. 具有 KILL DATABASE CONNECTION 权限的用户可使用 KILL 命令,显式终止与 SQL 数据库的连接。A user with the KILL DATABASE CONNECTION permission can explicitly terminate a connection to SQL Database by using the KILL command. 有关详细信息,请参阅 KILLFor more information, see KILL.

重要

当一个登录名(或包含的数据库用户)进行连接并经过身份验证后,该连接缓存有关该登录名的标识信息。When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. 对于 Windows 身份验证登录,此标识信息包含有关 Windows 组中的成员身份的信息。For a Windows Authentication login, this includes information about membership in Windows groups. 只要保持连接,该登录名的标识就保持已经过身份验证的状态。The identity of the login remains authenticated as long as the connection is maintained. 若要在标识中强制进行更改(例如,重置密码或更改 Windows 组成员身份),则必须从身份验证机构(Windows 或 SQL ServerSQL Server)注销,然后再次登录。To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL ServerSQL Server), and log in again. sysadmin 固定服务器角色的成员或任何使用 ALTER ANY CONNECTION 权限的登录名都可以使用 KILL 命令结束连接,并强制登录名重新连接。A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management StudioSQL Server Management Studio 可以在打开与对象资源管理器和查询编辑器窗口之间的多个连接时重用连接信息。can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. 关闭所有连接可强制重新连接。Close all connections to force reconnection.

OLD_PASSWORD =' oldpassword ' 仅适用于 SQL ServerSQL Server 登录名。OLD_PASSWORD ='oldpassword' Applies only to SQL ServerSQL Server logins. 要指派新密码的登录的当前密码。The current password of the login to which a new password will be assigned. 密码是区分大小写的。Passwords are case-sensitive.

NAME = login_name 正在重命名的登录的新名称。NAME = login_name The new name of the login that is being renamed. 如果是 Windows 登录,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL ServerSQL Server 中的登录相关联的 SID。If this is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL ServerSQL Server. SQL ServerSQL Server 登录名的新名称不能包含反斜杠字符 (\)。The new name of a SQL ServerSQL Server login cannot contain a backslash character (\).

RemarksRemarks

SQL 数据库SQL Database中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL 数据库SQL Database, login data required to authenticate a connection and server-level firewall rules are 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.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限 :If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • 在不提供旧密码的情况下重置密码。Resetting the password without supplying the old password.
  • 更改登录名。Changing the login name.
  • 启用或禁用登录名。Enabling or disabling the login.
  • 将登录名映射到其他凭据。Mapping the login to a different credential.

主体可更改自己的登录密码。A principal can change the password for its own login.

示例Examples

这些示例还包括使用其他 SQL 产品的示例。These examples also include examples for using other SQL products. 请参阅上述支持的参数。Please see which arguments are supported above.

A.A. 启用已禁用的登录名Enabling a disabled login

以下示例将启用 Mary5 登录名。The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B.B. 更改登录密码Changing the password of a login

以下示例将登录名 Mary5 的密码更改为强密码。The following example changes the password of login Mary5 to a strong password.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C.C. 更改登录名称Changing the name of a login

以下示例将 Mary5 登录名称更改为 John2The following example changes the name of login Mary5 to John2.

ALTER LOGIN Mary5 WITH NAME = John2;

D.D. 将登录名映射到凭据Mapping a login to a credential

以下示例将登录名 John2 映射到凭据 Custodian04The following example maps the login John2 to the credential Custodian04.

ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;

E.E. 将登录名映射到可扩展密钥管理凭据Mapping a login to an Extensible Key Management credential

以下示例将登录名 Mary5 映射到 EKM 凭据 EKMProvider1The following example maps the login Mary5 to the EKM credential EKMProvider1.

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

ALTER LOGIN Mary5
ADD CREDENTIAL EKMProvider1;
GO

F.F. 解除锁定登录名Unlocking a login

若要解除锁定 SQL ServerSQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。To unlock a SQL ServerSQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;

GO

若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO

G.G. 使用 HASHED 更改登录名的密码Changing the password of a login using HASHED

以下示例将 TestUser 登录名的密码更改为已经过哈希运算的值。The following example changes the password of the TestUser login to an already hashed value.

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

ALTER LOGIN TestUser WITH
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;
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

ALTER LOGIN login_name
    {
    <status_option>
    | WITH <set_option> [ ,... ]
    }

<status_option> ::=ENABLE | DISABLE

<set_option> ::=
    PASSWORD ='password'
    [
      OLD_PASSWORD ='oldpassword'
      | <password_option> [<password_option> ]
    ]
    | NAME = login_name
    | CHECK_POLICY = { ON | OFF }
    | CHECK_EXPIRATION = { ON | OFF }

<password_option> ::=
    MUST_CHANGE | UNLOCK

参数Arguments

login_name 指定正在更改的 SQL ServerSQL Server 登录名的名称。login_name Specifies the name of the SQL ServerSQL Server login that is being changed. 域登录名必须用方括号括起来,其格式为 [domain\user]。Domain logins must be enclosed in brackets in the format [domain\user].

ENABLE | DISABLE 启用或禁用此登录名。ENABLE | DISABLE Enables or disables this login. 禁用登录名不会影响已连接登录名的行为。Disabling a login does not affect the behavior of logins that are already connected. (使用 KILL 语句终止现有连接。)禁用的登录名将保留它们的权限,且仍然可以模拟。(Use the KILL statement to terminate an existing connection.) Disabled logins retain their permissions and can still be impersonated.

PASSWORD =' password ' 仅适用于 SQL ServerSQL Server 登录名。PASSWORD ='password' Applies only to SQL ServerSQL Server logins. 指定正在更改的登录名的密码。Specifies the password for the login that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

重要

当一个登录名(或包含的数据库用户)进行连接并经过身份验证后,该连接缓存有关该登录名的标识信息。When a login (or a contained database user) connects and is authenticated, the connection caches identity information about the login. 对于 Windows 身份验证登录,此标识信息包含有关 Windows 组中的成员身份的信息。For a Windows Authentication login, this includes information about membership in Windows groups. 只要保持连接,该登录名的标识就保持已经过身份验证的状态。The identity of the login remains authenticated as long as the connection is maintained. 若要在标识中强制进行更改(例如,重置密码或更改 Windows 组成员身份),则必须从身份验证机构(Windows 或 SQL ServerSQL Server)注销,然后再次登录。To force changes in the identity, such as a password reset or change in Windows group membership, the login must logoff from the authentication authority (Windows or SQL ServerSQL Server), and log in again. sysadmin 固定服务器角色的成员或任何使用 ALTER ANY CONNECTION 权限的登录名都可以使用 KILL 命令结束连接,并强制登录名重新连接。A member of the sysadmin fixed server role or any login with the ALTER ANY CONNECTION permission can use the KILL command to end a connection and force a login to reconnect. SQL Server Management StudioSQL Server Management Studio 可以在打开与对象资源管理器和查询编辑器窗口之间的多个连接时重用连接信息。can reuse connection information when opening multiple connections to Object Explorer and Query Editor windows. 关闭所有连接可强制重新连接。Close all connections to force reconnection.

OLD_PASSWORD =' oldpassword ' 仅适用于 SQL ServerSQL Server 登录名。OLD_PASSWORD ='oldpassword' Applies only to SQL ServerSQL Server logins. 要指派新密码的登录的当前密码。The current password of the login to which a new password will be assigned. 密码是区分大小写的。Passwords are case-sensitive.

MUST_CHANGE 仅适用于 SQL ServerSQL Server 登录名。MUST_CHANGE Applies only to SQL ServerSQL Server logins. 如果包括此选项,则 SQL ServerSQL Server 将在首次使用已更改的登录名时提示输入更新的密码。If this option is included, SQL ServerSQL Server will prompt for an updated password the first time the altered login is used.

NAME = login_name 正在重命名的登录的新名称。NAME = login_name The new name of the login that is being renamed. 如果使用 Windows 登录名,则与新名称对应的 Windows 主体的 SID 必须匹配与 SQL ServerSQL Server 中的登录名关联的 SID。If the login is a Windows login, the SID of the Windows principal corresponding to the new name must match the SID associated with the login in SQL ServerSQL Server. SQL ServerSQL Server 登录名的新名称不能包含反斜杠字符 (\)。The new name of a SQL ServerSQL Server login cannot contain a backslash character (\).

CHECK_EXPIRATION = { ON | OFF } 仅适用于 SQL ServerSQL Server 登录名。CHECK_EXPIRATION = { ON | OFF } Applies only to SQL ServerSQL Server logins. 指定是否应对此登录帐户强制实施密码过期策略。Specifies whether password expiration policy should be enforced on this login. 默认值为 OFF。The default value is OFF.

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

UNLOCK 仅适用于 SQL ServerSQL Server 登录名。UNLOCK Applies only to SQL ServerSQL Server logins. 指定应解锁被锁定的登录名。Specifies that a login that is locked out should be unlocked.

RemarksRemarks

如果 CHECK_POLICY 设置为 ON,则无法使用 HASHED 参数。When CHECK_POLICY is set to ON, the HASHED argument cannot be used.

如果 CHECK_POLICY 更改为 ON,则将出现以下行为:When CHECK_POLICY is changed to ON, the following behavior occurs:

  • 用当前的密码哈希值初始化密码历史记录。The password history is initialized with the value of the current password hash.

    如果 CHECK_POLICY 更改为 OFF,则将出现以下行为:When CHECK_POLICY is changed to OFF, the following behavior occurs:

  • CHECK_EXPIRATION 也设置为 OFF。CHECK_EXPIRATION is also set to OFF.

  • 清除密码历史记录。The password history is cleared.

  • 重置 lockout_time 的值 。The value of lockout_time is reset.

如果指定 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。If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. 包含此选项组合的 ALTER LOGIN 语句将失败。An ALTER LOGIN statement that has this combination of options will fail.

不能使用带 DISABLE 参数的 ALTER_LOGIN 来拒绝对 Windows 组的访问。You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. 这是设计的结果。This is by design. 例如,ALTER_LOGIN [domain\group] DISABLE 将返回下列错误信息 :For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:

`"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login '*Domain\Group*', because it does not exist or you do not have permission."`

SQL 数据库SQL Database中,对连接和服务器级别的防火墙规则进行身份验证时所需的登录数据会暂时缓存在每个数据库中。In SQL 数据库SQL Database, login data required to authenticate a connection and server-level firewall rules are 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.

权限Permissions

需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

如果使用 CREDENTIAL 选项,则还需要 ALTER ANY CREDENTIAL 权限。If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission.

如果正在更改的登录名是 sysadmin 固定服务器角色的成员或 CONTROL SERVER 权限的被授权者,则进行以下更改时还需要 CONTROL SERVER 权限 :If the login that is being changed is a member of the sysadmin fixed server role or a grantee of CONTROL SERVER permission, also requires CONTROL SERVER permission when making the following changes:

  • 在不提供旧密码的情况下重置密码。Resetting the password without supplying the old password.
  • 启用 MUST_CHANGE、CHECK_POLICY 或 CHECK_EXPIRATION。Enabling MUST_CHANGE, CHECK_POLICY, or CHECK_EXPIRATION.
  • 更改登录名。Changing the login name.
  • 启用或禁用登录名。Enabling or disabling the login.
  • 将登录名映射到其他凭据。Mapping the login to a different credential.

主体可更改用于自身登录的密码、默认语言以及默认数据库。A principal can change the password, default language, and default database for its own login.

示例Examples

这些示例还包括使用其他 SQL 产品的示例。These examples also include examples for using other SQL products. 请参阅上述支持的参数。Please see which arguments are supported above.

A.A. 启用已禁用的登录名Enabling a disabled login

以下示例将启用 Mary5 登录名。The following example enables the login Mary5.

ALTER LOGIN Mary5 ENABLE;

B.B. 更改登录密码Changing the password of a login

以下示例将登录名 Mary5 的密码更改为强密码。The following example changes the password of login Mary5 to a strong password.

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

C.C. 更改登录名称Changing the name of a login

以下示例将 Mary5 登录名称更改为 John2The following example changes the name of login Mary5 to John2.

ALTER LOGIN Mary5 WITH NAME = John2;

D.D. 将登录名映射到凭据Mapping a login to a credential

以下示例将登录名 John2 映射到凭据 Custodian04The following example maps the login John2 to the credential Custodian04.

ALTER LOGIN John2 WITH CREDENTIAL = Custodian04;

E.E. 将登录名映射到可扩展密钥管理凭据Mapping a login to an Extensible Key Management credential

以下示例将登录名 Mary5 映射到 EKM 凭据 EKMProvider1The following example maps the login Mary5 to the EKM credential EKMProvider1.

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

ALTER LOGIN Mary5
ADD CREDENTIAL EKMProvider1;
GO

F.F. 解除锁定登录名Unlocking a login

若要解除锁定 SQL ServerSQL Server 登录名,请执行以下语句,并将 **** 替换为所需帐户密码。To unlock a SQL ServerSQL Server login, execute the following statement, replacing **** with the desired account password.

ALTER LOGIN [Mary5] WITH PASSWORD = '****' UNLOCK ;

GO

若要在不更改密码的情况下解除锁定登录名,请关闭检查策略,然后再打开此检查策略。To unlock a login without changing the password, turn the check policy off and then on again.

ALTER LOGIN [Mary5] WITH CHECK_POLICY = OFF;
ALTER LOGIN [Mary5] WITH CHECK_POLICY = ON;
GO

G.G. 使用 HASHED 更改登录名的密码Changing the password of a login using HASHED

以下示例将 TestUser 登录名的密码更改为已经过哈希运算的值。The following example changes the password of the TestUser login to an already hashed value.

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

ALTER LOGIN TestUser WITH
PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED ;
GO

另请参阅See Also