ALTER USER (Transact-SQL)ALTER USER (Transact-SQL)

重命名数据库用户或更改它的默认架构。Renames a database user or changes its default schema.

“主题链接”图标 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 USER userName
     WITH <set_item> [ ,...n ]  
[;]  

<set_item> ::=
      NAME = newUserName
    | DEFAULT_SCHEMA = { schemaName | NULL }  
    | LOGIN = loginName  
    | PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]  
    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }  
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]  

参数Arguments

userNameuserName
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database.

LOGIN = loginName LOGIN = loginName
通过将用户的安全标识符 (SID) 更改为另一个登录名的 SID,使用户重新映射到该登录名。Remaps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

NAME =newUserName NAME = newUserName
指定此用户的新名称。Specifies the new name for this user. newUserName 不能已存在于当前数据库中 。newUserName must not already occur in the current database.

DEFAULT_SCHEMA = { schemaName | NULL } DEFAULT_SCHEMA = { schemaName | NULL }
指定服务器在解析此用户的对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. 将默认架构设置为 NULL 将从 Windows 组中删除默认架构。Setting the default schema to NULL removes a default schema from a Windows group. Windows 用户不能使用 NULL 选项。The NULL option cannot be used with a Windows user.

PASSWORD = 'password' PASSWORD = 'password'
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, SQL 数据库SQL Database.

指定正在更改的用户的密码。Specifies the password for the user that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

备注

此选项仅适用于包含的用户。This option is available only for contained users. 有关详细信息,请参阅包含的数据库sp_migrate_user_to_contained (Transact-SQL)For more information, see Contained Databases and sp_migrate_user_to_contained (Transact-SQL).

OLD_PASSWORD ='oldpassword' OLD_PASSWORD ='oldpassword'
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, SQL 数据库SQL Database.

将替换为“password”的当前用户密码 。The current user password that will be replaced by 'password'. 密码是区分大小写的。Passwords are case-sensitive. 除非拥有 ALTER ANY USER 权限,否则需要具有 OLD_PASSWORD 才能更改密码 。OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission. 需要 OLD_PASSWORD 可防止拥有 IMPERSONATION 权限的用户更改密码 。Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password.

备注

此选项仅适用于包含的用户。This option is available only for contained users.

DEFAULT_LANGUAGE ={ NONE | <lcid> | <language name> | <language alias> } DEFAULT_LANGUAGE ={ NONE | <lcid> | <language name> | <language alias> }
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

指定将指派给用户的默认语言。Specifies a default language to be assigned to the user. 如果将此选项设置为 NONE,则默认语言将设置为数据库的当前默认语言。If this option is set to NONE, the default language is set to the current default language of the database. 如果之后更改数据库的默认语言,用户的默认语言将保持不变。If the default language of the database is later changed, the default language of the user will remain unchanged. DEFAULT_LANGUAGE 可以为本地 ID (lcid)、语言的名称或语言别名 。DEFAULT_LANGUAGE can be the local ID (lcid), the name of the language, or the language alias.

备注

此选项只能在包含数据库中指定,且只能用于包含的用户。This option may only be specified in a contained database and only for contained users.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]
适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, SQL 数据库SQL Database.

取消在大容量复制操作期间对服务器进行加密元数据检查。Suppresses cryptographic metadata checks on the server in bulk copy operations. 这使用户能够在表或数据库之间大容量复制加密数据,而无需对数据进行解密。This enables the user to bulk copy encrypted data between tables or databases, without decrypting the data. 默认为 OFF。The default is OFF.

警告

错误使用此选项可能导致数据损坏。Improper use of this option can lead to data corruption. 有关详细信息,请参阅迁移通过 Always Encrypted 保护的敏感数据For more information, see Migrate Sensitive Data Protected by Always Encrypted.

RemarksRemarks

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will be used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. 如果不能为用户确定默认架构,则将使用 dbo 架构 。If no default schema can be determined for a user, the dbo schema will be used.

可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。DEFAULT_SCHEMA can be set to a schema that doesn't currently occur in the database. 因此,可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created.

不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA can't be specified for a user who is mapped to a certificate, or an asymmetric key.

重要

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值 。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

仅当新用户名的 SID 与在数据库中记录的 SID 匹配时,才能更改映射到 Windows 登录名或组的用户的名称。You can change the name of a user who is mapped to a Windows login or group only when the SID of the new user name matches the SID that is recorded in the database. 此检查将帮助防止数据库中的 Windows 登录名欺骗。This check helps prevent spoofing of Windows logins in the database.

使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。The WITH LOGIN clause enables the remapping of a user to a different login. 不能使用此子句重新映射以下用户:不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。Users without a login, users mapped to a certificate, or users mapped to an asymmetric key can't be remapped with this clause. 只能重新映射 SQL 用户和 Windows 用户(或组)。Only SQL users and Windows users (or groups) can be remapped. 不能使用 WITH LOGIN 子句更改用户类型,例如将 Windows 帐户更改为 SQL ServerSQL Server 登录名。The WITH LOGIN clause can't be used to change the type of user, such as changing a Windows account to a SQL ServerSQL Server login.

如果满足以下条件,则用户的名称会自动重命名为登录名。The name of the user will be automatically renamed to the login name if the following conditions are true.

  • 用户是一个 Windows 用户。The user is a Windows user.

  • 名称是一个 Windows 名称(包含反斜杠)。The name is a Windows name (contains a backslash).

  • 未指定新名称。No new name was specified.

  • 当前名称不同于登录名。The current name differs from the login name.

如果不满足上述条件,则不会重命名用户,除非调用方另外调用了 NAME 子句。Otherwise, the user won't be renamed unless the caller additionally invokes the NAME clause.

被映射到 SQL ServerSQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 (\)。The name of a user mapped to a SQL ServerSQL Server login, a certificate, or an asymmetric key can't contain the backslash character (\).

注意

从 SQL Server 2005 开始,架构的行为发生了更改。Beginning with SQL Server 2005, the behavior of schemas changed. 因此,假设架构与数据库用户等价的代码不再返回正确的结果。As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中:CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA、CREATE USER、ALTER USER、DROP USER、CREATE ROLE、ALTER ROLE、DROP ROLE、CREATE APPROLE、ALTER APPROLE、DROP APPROLE、ALTER AUTHORIZATION。Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. 在这类数据库中,必须改用新目录视图。In such databases you must instead use the new catalog views. 新的目录视图将采用在 SQL Server 2005 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

SecuritySecurity

备注

拥有 ALTER ANY USER 权限的用户可以更改任何用户的默认架构 。A user who has ALTER ANY USER permission can change the default schema of any user. 更改了架构的用户可能会在不知情的情况下从错误表中选择数据,或者从错误架构中执行代码。A user who has an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.

权限Permissions

更改用户名需要具有 ALTER ANY USER 权限 。To change the name of a user requires the ALTER ANY USER permission.

更改用户的目标登录名需要对数据库拥有 CONTROL 权限 。To change the target login of a user requires the CONTROL permission on the database.

若要更改对数据库拥有 CONTROL 权限的用户名名称,则需要对数据库拥有 CONTROL 权限 。To change the user name of a user having CONTROL permission on the database requires the CONTROL permission on the database.

更改默认架构或语言需要对用户拥有 ALTER 权限 。To change the default schema or language requires ALTER permission on the user. 用户可更改自己的默认架构或语言。Users can change their own default schema or language.

示例Examples

所有示例都在用户数据库中执行。All examples are executed in a user database.

A.A. 更改数据库用户的名称Changing the name of a database user

以下示例将数据库用户 Mary5 的名称更改为 Mary51The following example changes the name of the database user Mary5 to Mary51.

ALTER USER Mary5 WITH NAME = Mary51;  
GO  

B.B. 更改用户的默认架构Changing the default schema of a user

以下示例将用户 Mary51 的默认架构更改为 PurchasingThe following example changes the default schema of the user Mary51 to Purchasing.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;  
GO  

C.C. 同时更改几个选项Changing several options at once

以下示例可在一个语句中为一个包含数据库用户更改若干个选项。The following example changes several options for a contained database user in one statement.

适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

ALTER USER Philip
WITH NAME = Philipe
    , DEFAULT_SCHEMA = Development
    , PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per'
    , DEFAULT_LANGUAGE  = French ;  
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

ALTER USER userName
     WITH <set_item> [ ,...n ]  

<set_item> ::=
      NAME = newUserName
    | DEFAULT_SCHEMA = schemaName  
    | LOGIN = loginName  
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]
[;]  

-- Azure SQL Database Update Syntax  
ALTER USER userName
     WITH <set_item> [ ,...n ]  
[;]  

<set_item> ::=
      NAME = newUserName
    | DEFAULT_SCHEMA = { schemaName | NULL }  
    | LOGIN = loginName  
    | PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]  
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]

-- SQL Database syntax when connected to a federation member  
ALTER USER userName  
     WITH <set_item> [ ,... n ]
[;]  

<set_item> ::=
     NAME = newUserName  

参数Arguments

userNameuserName
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database.

LOGIN = loginName LOGIN = loginName
通过将用户的安全标识符 (SID) 更改为另一个登录名的 SID,使用户重新映射到该登录名。Remaps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

如果 ALTER USER 语句是 SQL 批处理中唯一的语句,则 Azure SQL 数据库将支持 WITH LOGIN 子句。If the ALTER USER statement is the only statement in a SQL batch, Azure SQL Database supports the WITH LOGIN clause. 如果 ALTER USER 语句不是 SQL 批处理中唯一的语句或在动态 SQL 中执行,则不支持 WITH LOGIN 子句。If the ALTER USER statement isn't the only statement in a SQL batch or is executed in dynamic SQL, the WITH LOGIN clause isn't supported.

NAME =newUserName NAME = newUserName
指定此用户的新名称。Specifies the new name for this user. newUserName 不能已存在于当前数据库中 。newUserName must not already occur in the current database.

DEFAULT_SCHEMA = { schemaName | NULL } DEFAULT_SCHEMA = { schemaName | NULL }
指定服务器在解析此用户的对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. 将默认架构设置为 NULL 将从 Windows 组中删除默认架构。Setting the default schema to NULL removes a default schema from a Windows group. Windows 用户不能使用 NULL 选项。The NULL option can't be used with a Windows user.

PASSWORD = 'password' PASSWORD = 'password'
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, SQL 数据库SQL Database.

指定正在更改的用户的密码。Specifies the password for the user that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

备注

此选项仅适用于包含的用户。This option is available only for contained users. 有关详细信息,请参阅包含的数据库sp_migrate_user_to_contained (Transact-SQL)For more information, see Contained Databases and sp_migrate_user_to_contained (Transact-SQL).

OLD_PASSWORD ='oldpassword' OLD_PASSWORD ='oldpassword'
适用于SQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later, SQL 数据库SQL Database.

将替换为“password”的当前用户密码 。The current user password that will be replaced by 'password'. 密码是区分大小写的。Passwords are case-sensitive. 除非拥有 ALTER ANY USER 权限,否则需要具有 OLD_PASSWORD 才能更改密码 。OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission. 需要 OLD_PASSWORD 可防止拥有 IMPERSONATION 权限的用户更改密码 。Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password.

备注

此选项仅适用于包含的用户。This option is available only for contained users.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]
适用于SQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本、SQL 数据库SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, SQL 数据库SQL Database.

取消在大容量复制操作期间对服务器进行加密元数据检查。Suppresses cryptographic metadata checks on the server in bulk copy operations. 这使用户能够在表或数据库之间大容量复制加密数据,而无需对数据进行解密。This enables the user to bulk copy encrypted data between tables or databases, without decrypting the data. 默认为 OFF。The default is OFF.

警告

错误使用此选项可能导致数据损坏。Improper use of this option can lead to data corruption. 有关详细信息,请参阅迁移通过 Always Encrypted 保护的敏感数据For more information, see Migrate Sensitive Data Protected by Always Encrypted.

RemarksRemarks

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. 如果不能为用户确定默认架构,则将使用 dbo 架构 。If no default schema can be determined for a user, the dbo schema will be used.

可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。DEFAULT_SCHEMA can be set to a schema that doesn't currently occur in the database. 因此,可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created.

不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA can't be specified for a user who is mapped to a certificate, or an asymmetric key.

重要

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值 。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

仅当新用户名的 SID 与在数据库中记录的 SID 匹配时,才能更改映射到 Windows 登录名或组的用户的名称。You can change the name of a user who is mapped to a Windows login or group only when the SID of the new user name matches the SID that is recorded in the database. 此检查将帮助防止数据库中的 Windows 登录名欺骗。This check helps prevent spoofing of Windows logins in the database.

使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。The WITH LOGIN clause enables the remapping of a user to a different login. 不能使用此子句重新映射以下用户:不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。Users without a login, users mapped to a certificate, or users mapped to an asymmetric key can't be remapped with this clause. 只能重新映射 SQL 用户和 Windows 用户(或组)。Only SQL users and Windows users (or groups) can be remapped. 不能使用 WITH LOGIN 子句更改用户类型,例如将 Windows 帐户更改为 SQL ServerSQL Server 登录名。The WITH LOGIN clause can't be used to change the type of user, such as changing a Windows account to a SQL ServerSQL Server login.

如果满足以下条件,则用户的名称会自动重命名为登录名。The name of the user will be automatically renamed to the login name if the following conditions are true.

  • 用户是一个 Windows 用户。The user is a Windows user.

  • 名称是一个 Windows 名称(包含反斜杠)。The name is a Windows name (contains a backslash).

  • 未指定新名称。No new name was specified.

  • 当前名称不同于登录名。The current name differs from the login name.

如果不满足上述条件,则不会重命名用户,除非调用方另外调用了 NAME 子句。Otherwise, the user won't be renamed unless the caller additionally invokes the NAME clause.

被映射到 SQL ServerSQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 (\)。The name of a user mapped to a SQL ServerSQL Server login, a certificate, or an asymmetric key can't contain the backslash character (\).

注意

从 SQL Server 2005 开始,架构的行为发生了更改。Beginning with SQL Server 2005, the behavior of schemas changed. 因此,假设架构与数据库用户等价的代码不再返回正确的结果。As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中:CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA、CREATE USER、ALTER USER、DROP USER、CREATE ROLE、ALTER ROLE、DROP ROLE、CREATE APPROLE、ALTER APPROLE、DROP APPROLE、ALTER AUTHORIZATION。Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. 在这类数据库中,必须改用新目录视图。In such databases you must instead use the new catalog views. 新的目录视图将采用在 SQL Server 2005 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

SecuritySecurity

备注

拥有 ALTER ANY USER 权限的用户可以更改任何用户的默认架构 。A user who has ALTER ANY USER permission can change the default schema of any user. 更改了架构的用户可能会在不知情的情况下从错误表中选择数据,或者从错误架构中执行代码。A user who has an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.

权限Permissions

更改用户名需要具有 ALTER ANY USER 权限 。To change the name of a user requires the ALTER ANY USER permission.

更改用户的目标登录名需要对数据库拥有 CONTROL 权限 。To change the target login of a user requires the CONTROL permission on the database.

若要更改对数据库拥有 CONTROL 权限的用户名名称,则需要对数据库拥有 CONTROL 权限 。To change the user name of a user having CONTROL permission on the database requires the CONTROL permission on the database.

更改默认架构或语言需要对用户拥有 ALTER 权限 。To change the default schema or language requires ALTER permission on the user. 用户可更改自己的默认架构或语言。Users can change their own default schema or language.

示例Examples

所有示例都在用户数据库中执行。All examples are executed in a user database.

A.A. 更改数据库用户的名称Changing the name of a database user

以下示例将数据库用户 Mary5 的名称更改为 Mary51The following example changes the name of the database user Mary5 to Mary51.

ALTER USER Mary5 WITH NAME = Mary51;  
GO  

B.B. 更改用户的默认架构Changing the default schema of a user

以下示例将用户 Mary51 的默认架构更改为 PurchasingThe following example changes the default schema of the user Mary51 to Purchasing.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;  
GO  

C.C. 同时更改几个选项Changing several options at once

以下示例可在一个语句中为一个包含数据库用户更改若干个选项。The following example changes several options for a contained database user in one statement.

ALTER USER Philip
WITH  NAME = Philipe
    , DEFAULT_SCHEMA = Development
    , PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per';
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

重要

Azure SQL 数据库托管实例应用于具有 Azure AD 登录名的用户时,仅支持以下选项:DEFAULT_SCHEMA = { schemaName | NULL }DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }Only the following options are supported for Azure SQL Database managed instance when applying to users with Azure AD logins: DEFAULT_SCHEMA = { schemaName | NULL } and DEFAULT_LANGUAGE = { NONE | lcid | language name | language alias }

添加了新的语法扩展,有助于重新映射已迁移到托管实例的数据库中的用户。There is a new syntax extension that was added to help remap users in a database that was migrated to managed instance. ALTER USER 语法有助于将通过 Azure AD 联合并同步的域中的数据库用户映射到 Azure AD 登录名。The ALTER USER syntax helps map database users in a federated and synchronized domain with Azure AD, to Azure AD logins.

-- Syntax for Azure SQL Database managed instance
ALTER USER userName
     { WITH <set_item> [ ,...n ] | FROM EXTERNAL PROVIDER }
[;]  

<set_item> ::=
      NAME = newUserName
    | DEFAULT_SCHEMA = { schemaName | NULL }  
    | LOGIN = loginName  
    | PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]
    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }
    | ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]

-- Users or groups that are migrated as federated and synchronized with Azure AD have the following syntax:

    /** Applies to Windows users that were migrated and have the following user names:
    - Windows user <domain\user>
    - Windows group <domain\MyWindowsGroup>
    - Windows alias <MyWindowsAlias>
    **/

ALTER USER userName  
     { WITH <set_item> [ ,...n ] | FROM EXTERNAL PROVIDER }
[;]  

<set_item> ::=
     NAME = newUserName
    | DEFAULT_SCHEMA = { schemaName | NULL }
    | LOGIN = loginName
    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }

参数Arguments

userNameuserName
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database.

LOGIN = loginName LOGIN = loginName
通过将用户的安全标识符 (SID) 更改为另一个登录名的 SID,使用户重新映射到该登录名。Remaps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

如果 ALTER USER 语句是 SQL 批处理中唯一的语句,则 Azure SQL 数据库将支持 WITH LOGIN 子句。If the ALTER USER statement is the only statement in a SQL batch, Azure SQL Database supports the WITH LOGIN clause. 如果 ALTER USER 语句不是 SQL 批处理中唯一的语句或在动态 SQL 中执行,则不支持 WITH LOGIN 子句。If the ALTER USER statement isn't the only statement in a SQL batch or is executed in dynamic SQL, the WITH LOGIN clause isn't supported.

NAME =newUserName NAME = newUserName
指定此用户的新名称。Specifies the new name for this user. newUserName 不能已存在于当前数据库中 。newUserName must not already occur in the current database.

DEFAULT_SCHEMA = { schemaName | NULL } DEFAULT_SCHEMA = { schemaName | NULL }
指定服务器在解析此用户的对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. 将默认架构设置为 NULL 将从 Windows 组中删除默认架构。Setting the default schema to NULL removes a default schema from a Windows group. Windows 用户不能使用 NULL 选项。The NULL option can't be used with a Windows user.

PASSWORD = 'password' PASSWORD = 'password'

指定正在更改的用户的密码。Specifies the password for the user that is being changed. 密码是区分大小写的。Passwords are case-sensitive.

备注

此选项仅适用于包含的用户。This option is available only for contained users. 有关详细信息,请参阅包含的数据库sp_migrate_user_to_contained (Transact-SQL)For more information, see Contained Databases and sp_migrate_user_to_contained (Transact-SQL).

OLD_PASSWORD = 'oldpassword' OLD_PASSWORD = 'oldpassword'

将替换为“password”的当前用户密码 。The current user password that will be replaced by 'password'. 密码是区分大小写的。Passwords are case-sensitive. 除非拥有 ALTER ANY USER 权限,否则需要具有 OLD_PASSWORD 才能更改密码 。OLD_PASSWORD is required to change a password, unless you have ALTER ANY USER permission. 需要 OLD_PASSWORD 可防止拥有 IMPERSONATION 权限的用户更改密码 。Requiring OLD_PASSWORD prevents users with IMPERSONATION permission from changing the password.

备注

此选项仅适用于包含的用户。This option is available only for contained users.

DEFAULT_LANGUAGE ={ NONE | <lcid> | <language name> | <language alias> } DEFAULT_LANGUAGE ={ NONE | <lcid> | <language name> | <language alias> }

指定将指派给用户的默认语言。Specifies a default language to be assigned to the user. 如果将此选项设置为 NONE,则默认语言将设置为数据库的当前默认语言。If this option is set to NONE, the default language is set to the current default language of the database. 如果之后更改数据库的默认语言,用户的默认语言将保持不变。If the default language of the database is later changed, the default language of the user will remain unchanged. DEFAULT_LANGUAGE 可以为本地 ID (lcid)、语言的名称或语言别名 。DEFAULT_LANGUAGE can be the local ID (lcid), the name of the language, or the language alias.

备注

此选项只能在包含数据库中指定,且只能用于包含的用户。This option may only be specified in a contained database and only for contained users.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = [ ON | OFF ]

取消在大容量复制操作期间对服务器进行加密元数据检查。Suppresses cryptographic metadata checks on the server in bulk copy operations. 这使用户能够在表或数据库之间大容量复制加密数据,而无需对数据进行解密。This enables the user to bulk copy encrypted data between tables or databases, without decrypting the data. 默认为 OFF。The default is OFF.

警告

错误使用此选项可能导致数据损坏。Improper use of this option can lead to data corruption. 有关详细信息,请参阅迁移通过 Always Encrypted 保护的敏感数据For more information, see Migrate Sensitive Data Protected by Always Encrypted.

RemarksRemarks

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. 如果不能为用户确定默认架构,则将使用 dbo 架构 。If no default schema can be determined for a user, the dbo schema will be used.

可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。DEFAULT_SCHEMA can be set to a schema that doesn't currently occur in the database. 因此,可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created.

不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA can't be specified for a user who is mapped to a certificate, or an asymmetric key.

重要

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值 。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

仅当新用户名的 SID 与在数据库中记录的 SID 匹配时,才能更改映射到 Windows 登录名或组的用户的名称。You can change the name of a user who is mapped to a Windows login or group only when the SID of the new user name matches the SID that is recorded in the database. 此检查将帮助防止数据库中的 Windows 登录名欺骗。This check helps prevent spoofing of Windows logins in the database.

使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。The WITH LOGIN clause enables the remapping of a user to a different login. 不能使用此子句重新映射以下用户:不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。Users without a login, users mapped to a certificate, or users mapped to an asymmetric key can't be remapped with this clause. 只能重新映射 SQL 用户和 Windows 用户(或组)。Only SQL users and Windows users (or groups) can be remapped. 不能使用 WITH LOGIN 子句更改用户类型,例如将 Windows 帐户更改为 SQL ServerSQL Server 登录名。The WITH LOGIN clause can't be used to change the type of user, such as changing a Windows account to a SQL ServerSQL Server login. 唯一的例外是将 Windows 用户更改为 Azure AD 用户。The only exception is when changing a Windows user to an Azure AD user.

备注

以下规则不适用于托管实例上的 Windows 用户,因为我们不支持在托管实例上创建 Windows 登录名。The following rules do not apply to Windows users on managed instance as we do not support creating Windows logins on managed instance. 仅当存在 Azure AD 登录名时,才能使用 WITH LOGIN 选项。The WITH LOGIN option can only be used if Azure AD logins are present.

如果满足以下条件,则用户的名称会自动重命名为登录名。The name of the user will be automatically renamed to the login name if the following conditions are true.

  • 用户是一个 Windows 用户。The user is a Windows user.

  • 名称是一个 Windows 名称(包含反斜杠)。The name is a Windows name (contains a backslash).

  • 未指定新名称。No new name was specified.

  • 当前名称不同于登录名。The current name differs from the login name.

如果不满足上述条件,则不会重命名用户,除非调用方另外调用了 NAME 子句。Otherwise, the user won't be renamed unless the caller additionally invokes the NAME clause.

被映射到 SQL ServerSQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 (\)。The name of a user mapped to a SQL ServerSQL Server login, a certificate, or an asymmetric key can't contain the backslash character (\).

注意

从 SQL Server 2005 开始,架构的行为发生了更改。Beginning with SQL Server 2005, the behavior of schemas changed. 因此,假设架构与数据库用户等价的代码不再返回正确的结果。As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中:CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA、CREATE USER、ALTER USER、DROP USER、CREATE ROLE、ALTER ROLE、DROP ROLE、CREATE APPROLE、ALTER APPROLE、DROP APPROLE、ALTER AUTHORIZATION。Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. 在这类数据库中,必须改用新目录视图。In such databases you must instead use the new catalog views. 新的目录视图将采用在 SQL Server 2005 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

迁移到托管实例的 SQL 本地 Windows 用户的备注Remarks for Windows users in SQL on-premises migrated to managed instance

这些备注适用于作为已通过 Azure AD 联合并同步的 Windows 用户进行身份验证。These remarks apply to authenticating as Windows users that have been federated and synchronized with Azure AD.

备注

创建之后用于托管实例的 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.

  • 默认情况下,通过用于迁移目的的 ALTER USER 语法的所有版本中的图形 API 来验证映射到 Azure AD 的 Windows 用户或组。Validation of Windows users or groups that are mapped to Azure AD is done by default through Graph API in all versions of the ALTER USER syntax used for migration purpose.
  • 具有别名的本地用户(使用与原始 Windows 帐户不同的名称)将保留别名。On-premises users that were aliased (use a different name from the original Windows account) will keep the aliased name.
  • 对于 Azure AD 身份验证,LOGIN 参数仅适用于托管实例,不能与 SQL DB 一起使用。For Azure AD authentication, the LOGIN parameter applies only to managed instance and can't be used with SQL DB.
  • 若要查看 Azure AD 主体的登录名,请使用以下命令:select * from sys.server_principalsTo view logins for Azure AD Principals, use the following command: select * from sys.server_principals.
    • 检查登录名的指示类型是 E 还是 XCheck the login's indicated type is E or X.
  • PASSWORD 选项不能用于 Azure AD 用户。PASSWORD option can't be used for Azure AD users.
  • 在所有迁移情况下,Windows 用户或组的角色和权限将自动转移到新的 Azure AD 用户或组。In all migration cases, the roles and permissions of Windows users or groups will automatically be transferred to the new Azure AD users or groups.
  • 新的语法扩展 FROM EXTERNAL PROVIDER 可用于将 Windows 用户和组从 SQL 本地更改为 Azure AD 用户和组 。A new syntax extension, FROM EXTERNAL PROVIDER is available for altering Windows users and groups from SQL on-premises to Azure AD users and groups. 使用此扩展时,Windows 域必须通过 Azure AD 联合,且所有 Windows 域成员必须存在于 Azure AD 中。The Windows domain must be federated with Azure AD and all Windows domain members must exist in Azure AD when using this extension. FROM EXTERNAL PROVIDER 语法适用于托管实例,并且应在以下情况下使用:Windows 用户在原始 SQL 实例上没有登录名,并且需要映射到独立的 Azure AD 数据库用户 。The FROM EXTERNAL PROVIDER syntax applies to managed instance and should be used in case Windows users do not have logins on the original SQL instance and need to be mapped to standalone Azure AD database users.
    • 在这种情况下,允许的用户名可以是:In this case, the allowable userName can be:
      • Widows 用户(域\用户 )。A Widows user (domain\user).
      • Windows 组(MyWidnowsGroup )。A Windows group (MyWidnowsGroup).
      • Windows 别名(MyWindowsAlias )。A Windows alias (MyWindowsAlias).
    • ALTER 命令的结果会将旧用户名替换为基于旧用户名的原始 SID 在 Azure AD 中找到的相应名称。The outcome of the ALTER command replaces the old userName with the corresponding name that is found in Azure AD based on the original SID of the old userName. 更改的名称将被替换并存储在数据库的元数据中:The altered name is replaced and stored in the metadata of the database:
      • (域\用户 )将替换为 Azure AD user@domain.com。(domain\user) will be replaced with Azure AD user@domain.com.
      • (域\MyWidnowsGroup )将替换为 Azure AD 组。(domain\MyWidnowsGroup) will be replaced with Azure AD group.
      • (MyWindowsAlias )将保持不变,但会在 Azure AD 中检查此用户的 SID。(MyWindowsAlias) will remain unchanged but the SID of this user will be checked in Azure AD.

备注

如果在 Azure AD 中找不到转换为 objectID 的原始用户的 SID,则 ALTER USER 命令将失败。If the SID of the original user converted to objectID cannot be found in Azure AD, the ALTER USER command will fail.

  • 若要查看更改的用户,请使用以下命令:select * from sys.database_principalsTo view altered users, use the following command: select * from sys.database_principals
    • 检查用户的指示类型 E 还是 XCheck the user's indicated type E or X.
  • 当 NAME 用于将 Windows 用户迁移到 Azure AD 用户时,以下限制适用:When NAME is used to migrate Windows users to Azure AD users, the following restrictions apply:
    • 必须指定有效的 LOGIN。A valid LOGIN must be specified.
    • NAME 将在 Azure AD 中检查,并且只能是:The NAME will be checked in Azure AD and can only be:
      • LOGIN 的名称。The name of the LOGIN.
      • 别名 - 名称不能存在于 Azure AD 中。An alias - the name can't exist in Azure AD.
    • 在所有其他情况下,语法将失败。In all other cases, the syntax will fail.

SecuritySecurity

备注

拥有 ALTER ANY USER 权限的用户可以更改任何用户的默认架构 。A user who has ALTER ANY USER permission can change the default schema of any user. 更改了架构的用户可能会在不知情的情况下从错误表中选择数据,或者从错误架构中执行代码。A user who has an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.

权限Permissions

更改用户名需要具有 ALTER ANY USER 权限 。To change the name of a user requires the ALTER ANY USER permission.

更改用户的目标登录名需要对数据库拥有 CONTROL 权限 。To change the target login of a user requires the CONTROL permission on the database.

若要更改对数据库拥有 CONTROL 权限的用户名名称,则需要对数据库拥有 CONTROL 权限 。To change the user name of a user having CONTROL permission on the database requires the CONTROL permission on the database.

更改默认架构或语言需要对用户拥有 ALTER 权限 。To change the default schema or language requires ALTER permission on the user. 用户可更改自己的默认架构或语言。Users can change their own default schema or language.

示例Examples

所有示例都在用户数据库中执行。All examples are executed in a user database.

A.A. 更改数据库用户的名称Changing the name of a database user

以下示例将数据库用户 Mary5 的名称更改为 Mary51The following example changes the name of the database user Mary5 to Mary51.

ALTER USER Mary5 WITH NAME = Mary51;  
GO  

B.B. 更改用户的默认架构Changing the default schema of a user

以下示例将用户 Mary51 的默认架构更改为 PurchasingThe following example changes the default schema of the user Mary51 to Purchasing.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;  
GO  

C.C. 同时更改几个选项Changing several options at once

以下示例可在一个语句中为一个包含数据库用户更改若干个选项。The following example changes several options for a contained database user in one statement.

ALTER USER Philip
WITH NAME = Philipe
    , DEFAULT_SCHEMA = Development
    , PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per'
    , DEFAULT_LANGUAGE  = French ;  
GO  

D.D. 迁移后将数据库中的用户映射到 Azure AD 登录名Map the user in the database to an Azure AD login after migration

以下示例将用户 westus/joe 重新映射到 Azure AD 用户 joe@westus.comThe following example remaps the user, westus/joe to an Azure AD user, joe@westus.com. 此示例适用于已存在于托管实例中的登录名。This example is for logins that already exist in the managed instance. 完成到托管实例的数据库迁移之后,并且想要使用 Azure AD 登录名进行身份验证时,需要执行此操作。This needs to be performed after you have completed a database migration to managed instance, and want to use the Azure AD login to authenticate.

ALTER USER [westus/joe] WITH LOGIN = joe@westus.com

E.E. 将数据库中不具有托管实例中的登录名的旧 Windows 用户映射到 Azure AD 用户Map an old Windows user in the database without a login in managed instance to an Azure AD user

以下示例将不具有登录名的用户 westus/joe 重新映射到 Azure AD 用户 joe@westus.comThe following example remaps the user, westus/joe without a login, to an Azure AD user, joe@westus.com. 联合用户必须存在于 Azure AD 中。The federated user must exist in Azure AD.

ALTER USER [westus/joe] FROM EXTERNAL PROVIDER

F.F. 将用户别名映射到现有 Azure AD 登录名Map the user alias to an existing Azure AD login

以下示例将用户名 westus\joe 重新映射到 joe_aliasThe following example remaps the user name, westus\joe to joe_alias. 在这种情况下,相应的 Azure AD 登录名为 joe@westus.comThe corresponding Azure AD login in this case is joe@westus.com.

ALTER USER [westus/joe] WITH LOGIN = joe@westus.com, name= joe_alias  

G.G. 将托管实例中迁移的 Windows 组映射到 Azure AD 组Map a Windows group that was migrated in managed instance to an Azure AD group

以下示例将旧的本地组 westus\mygroup 重新映射到托管实例中的 Azure AD 组 mygroupThe following example remaps the old on-premises group, westus\mygroup to an Azure AD group mygroup in the managed instance. 组必须存在于 Azure AD 中。The group must exist in Azure AD.

ALTER USER [westus\mygroup] WITH LOGIN = mygroup

另请参阅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 and Parallel Data Warehouse  

ALTER USER userName
     WITH <set_item> [ ,...n ]  

<set_item> ::=
     NAME = newUserName
     | LOGIN = loginName  
     | DEFAULT_SCHEMA = schema_name  
[;]  

参数Arguments

userNameuserName
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database.

LOGIN = loginName LOGIN = loginName
通过将用户的安全标识符 (SID) 更改为另一个登录名的 SID,使用户重新映射到该登录名。Remaps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

如果 ALTER USER 语句是 SQL 批处理中唯一的语句,则 Azure SQL 数据库将支持 WITH LOGIN 子句。If the ALTER USER statement is the only statement in a SQL batch, Azure SQL Database supports the WITH LOGIN clause. 如果 ALTER USER 语句不是 SQL 批处理中唯一的语句或在动态 SQL 中执行,则不支持 WITH LOGIN 子句。If the ALTER USER statement isn't the only statement in a SQL batch or is executed in dynamic SQL, the WITH LOGIN clause isn't supported.

NAME =newUserName NAME = newUserName
指定此用户的新名称。Specifies the new name for this user. newUserName 不能已存在于当前数据库中 。newUserName must not already occur in the current database.

DEFAULT_SCHEMA = { schemaName | NULL } DEFAULT_SCHEMA = { schemaName | NULL }
指定服务器在解析此用户的对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. 将默认架构设置为 NULL 将从 Windows 组中删除默认架构。Setting the default schema to NULL removes a default schema from a Windows group. Windows 用户不能使用 NULL 选项。The NULL option can't be used with a Windows user.

RemarksRemarks

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. 如果不能为用户确定默认架构,则将使用 dbo 架构 。If no default schema can be determined for a user, the dbo schema will be used.

可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。DEFAULT_SCHEMA can be set to a schema that doesn't currently occur in the database. 因此,可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created.

不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA can't be specified for a user who is mapped to a certificate, or an asymmetric key.

重要

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值 。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。The WITH LOGIN clause enables the remapping of a user to a different login. 不能使用此子句重新映射以下用户:不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。Users without a login, users mapped to a certificate, or users mapped to an asymmetric key can't be remapped with this clause. 只能重新映射 SQL 用户和 Windows 用户(或组)。Only SQL users and Windows users (or groups) can be remapped. 不能使用 WITH LOGIN 子句更改用户类型,例如将 Windows 帐户更改为 SQL ServerSQL Server 登录名。The WITH LOGIN clause can't be used to change the type of user, such as changing a Windows account to a SQL ServerSQL Server login.

如果满足以下条件,则用户的名称会自动重命名为登录名。The name of the user will be automatically renamed to the login name if the following conditions are true.

  • 未指定新名称。No new name was specified.

  • 当前名称不同于登录名。The current name differs from the login name.

如果不满足上述条件,则不会重命名用户,除非调用方另外调用了 NAME 子句。Otherwise, the user won't be renamed unless the caller additionally invokes the NAME clause.

被映射到 SQL ServerSQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 (\)。The name of a user mapped to a SQL ServerSQL Server login, a certificate, or an asymmetric key can't contain the backslash character (\).

注意

从 SQL Server 2005 开始,架构的行为发生了更改。Beginning with SQL Server 2005, the behavior of schemas changed. 因此,假设架构与数据库用户等价的代码不再返回正确的结果。As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中:CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA、CREATE USER、ALTER USER、DROP USER、CREATE ROLE、ALTER ROLE、DROP ROLE、CREATE APPROLE、ALTER APPROLE、DROP APPROLE、ALTER AUTHORIZATION。Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. 在这类数据库中,必须改用新目录视图。In such databases you must instead use the new catalog views. 新的目录视图将采用在 SQL Server 2005 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

SecuritySecurity

备注

拥有 ALTER ANY USER 权限的用户可以更改任何用户的默认架构 。A user who has ALTER ANY USER permission can change the default schema of any user. 更改了架构的用户可能会在不知情的情况下从错误表中选择数据,或者从错误架构中执行代码。A user who has an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.

权限Permissions

更改用户名需要具有 ALTER ANY USER 权限 。To change the name of a user requires the ALTER ANY USER permission.

更改用户的目标登录名需要对数据库拥有 CONTROL 权限 。To change the target login of a user requires the CONTROL permission on the database.

若要更改对数据库拥有 CONTROL 权限的用户名名称,则需要对数据库拥有 CONTROL 权限 。To change the user name of a user having CONTROL permission on the database requires the CONTROL permission on the database.

更改默认架构或语言需要对用户拥有 ALTER 权限 。To change the default schema or language requires ALTER permission on the user. 用户可更改自己的默认架构或语言。Users can change their own default schema or language.

示例Examples

所有示例都在用户数据库中执行。All examples are executed in a user database.

A.A. 更改数据库用户的名称Changing the name of a database user

以下示例将数据库用户 Mary5 的名称更改为 Mary51The following example changes the name of the database user Mary5 to Mary51.

ALTER USER Mary5 WITH NAME = Mary51;  
GO  

B.B. 更改用户的默认架构Changing the default schema of a user

以下示例将用户 Mary51 的默认架构更改为 PurchasingThe following example changes the default schema of the user Mary51 to Purchasing.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;  
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 Azure SQL Data Warehouse and Parallel Data Warehouse  

ALTER USER userName
     WITH <set_item> [ ,...n ]  

<set_item> ::=
     NAME = newUserName
     | LOGIN = loginName  
     | DEFAULT_SCHEMA = schema_name  
[;]  

参数Arguments

userNameuserName
指定在此数据库中用于识别该用户的名称。Specifies the name by which the user is identified inside this database.

LOGIN = loginName LOGIN = loginName
通过将用户的安全标识符 (SID) 更改为另一个登录名的 SID,使用户重新映射到该登录名。Remaps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

如果 ALTER USER 语句是 SQL 批处理中唯一的语句,则 Azure SQL 数据库将支持 WITH LOGIN 子句。If the ALTER USER statement is the only statement in a SQL batch, Azure SQL Database supports the WITH LOGIN clause. 如果 ALTER USER 语句不是 SQL 批处理中唯一的语句或在动态 SQL 中执行,则不支持 WITH LOGIN 子句。If the ALTER USER statement isn't the only statement in a SQL batch or is executed in dynamic SQL, the WITH LOGIN clause isn't supported.

NAME =newUserName NAME = newUserName
指定此用户的新名称。Specifies the new name for this user. newUserName 不能已存在于当前数据库中 。newUserName must not already occur in the current database.

DEFAULT_SCHEMA = { schemaName | NULL } DEFAULT_SCHEMA = { schemaName | NULL }
指定服务器在解析此用户的对象名时将搜索的第一个架构。Specifies the first schema that will be searched by the server when it resolves the names of objects for this user. 将默认架构设置为 NULL 将从 Windows 组中删除默认架构。Setting the default schema to NULL removes a default schema from a Windows group. Windows 用户不能使用 NULL 选项。The NULL option can't be used with a Windows user.

RemarksRemarks

默认架构将是服务器为此数据库用户解析对象名时将搜索的第一个架构。The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user. 除非另外指定,否则默认架构将是此数据库用户创建的对象所属的架构。Unless otherwise specified, the default schema will be the owner of objects created by this database user.

如果用户具有默认架构,则将使用默认架构。If the user has a default schema, that default schema will used. 如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。If the user doesn't have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. 如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。If the user doesn't have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. 如果不能为用户确定默认架构,则将使用 dbo 架构 。If no default schema can be determined for a user, the dbo schema will be used.

可以将 DEFAULT_SCHEMA 设置为数据库中当前不存在的架构。DEFAULT_SCHEMA can be set to a schema that doesn't currently occur in the database. 因此,可以在创建架构之前将 DEFAULT_SCHEMA 分配给用户。Therefore, you can assign a DEFAULT_SCHEMA to a user before that schema is created.

不能为映射到证书或非对称密钥的用户指定 DEFAULT_SCHEMA。DEFAULT_SCHEMA can't be specified for a user who is mapped to a certificate, or an asymmetric key.

重要

如果用户是 sysadmin 固定服务器角色的成员,则忽略 DEFAULT_SCHEMA 的值 。The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. sysadmin 固定服务器角色的所有成员都有默认架构 dboAll members of the sysadmin fixed server role have a default schema of dbo.

使用 WITH LOGIN 子句可以将用户重新映射到一个不同的登录名。The WITH LOGIN clause enables the remapping of a user to a different login. 不能使用此子句重新映射以下用户:不具有登录名的用户、映射到证书的用户或映射到非对称密钥的用户。Users without a login, users mapped to a certificate, or users mapped to an asymmetric key can't be remapped with this clause. 只能重新映射 SQL 用户和 Windows 用户(或组)。Only SQL users and Windows users (or groups) can be remapped. 不能使用 WITH LOGIN 子句更改用户类型,例如将 Windows 帐户更改为 SQL ServerSQL Server 登录名。The WITH LOGIN clause can't be used to change the type of user, such as changing a Windows account to a SQL ServerSQL Server login.

如果满足以下条件,则用户的名称会自动重命名为登录名。The name of the user will be automatically renamed to the login name if the following conditions are true.

  • 未指定新名称。No new name was specified.

  • 当前名称不同于登录名。The current name differs from the login name.

如果不满足上述条件,则不会重命名用户,除非调用方另外调用了 NAME 子句。Otherwise, the user won't be renamed unless the caller additionally invokes the NAME clause.

被映射到 SQL ServerSQL Server 登录名、证书或非对称密钥的用户名不能包含反斜杠字符 (\)。The name of a user mapped to a SQL ServerSQL Server login, a certificate, or an asymmetric key can't contain the backslash character (\).

注意

从 SQL Server 2005 开始,架构的行为发生了更改。Beginning with SQL Server 2005, the behavior of schemas changed. 因此,假设架构与数据库用户等价的代码不再返回正确的结果。As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. 旧目录视图(包括 sysobjects)不应用于曾使用下列任何 DDL 语句的数据库中:CREATE SCHEMA、ALTER SCHEMA、DROP SCHEMA、CREATE USER、ALTER USER、DROP USER、CREATE ROLE、ALTER ROLE、DROP ROLE、CREATE APPROLE、ALTER APPROLE、DROP APPROLE、ALTER AUTHORIZATION。Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. 在这类数据库中,必须改用新目录视图。In such databases you must instead use the new catalog views. 新的目录视图将采用在 SQL Server 2005 中引入的使主体和架构分离的方法。The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. 有关目录视图的详细信息,请参阅目录视图 (Transact-SQL)For more information about catalog views, see Catalog Views (Transact-SQL).

SecuritySecurity

备注

拥有 ALTER ANY USER 权限的用户可以更改任何用户的默认架构 。A user who has ALTER ANY USER permission can change the default schema of any user. 更改了架构的用户可能会在不知情的情况下从错误表中选择数据,或者从错误架构中执行代码。A user who has an altered schema might unknowingly select data from the wrong table or execute code from the wrong schema.

权限Permissions

更改用户名需要具有 ALTER ANY USER 权限 。To change the name of a user requires the ALTER ANY USER permission.

更改用户的目标登录名需要对数据库拥有 CONTROL 权限 。To change the target login of a user requires the CONTROL permission on the database.

若要更改对数据库拥有 CONTROL 权限的用户名名称,则需要对数据库拥有 CONTROL 权限 。To change the user name of a user having CONTROL permission on the database requires the CONTROL permission on the database.

更改默认架构或语言需要对用户拥有 ALTER 权限 。To change the default schema or language requires ALTER permission on the user. 用户可更改自己的默认架构或语言。Users can change their own default schema or language.

示例Examples

所有示例都在用户数据库中执行。All examples are executed in a user database.

A.A. 更改数据库用户的名称Changing the name of a database user

以下示例将数据库用户 Mary5 的名称更改为 Mary51The following example changes the name of the database user Mary5 to Mary51.

ALTER USER Mary5 WITH NAME = Mary51;  
GO  

B.B. 更改用户的默认架构Changing the default schema of a user

以下示例将用户 Mary51 的默认架构更改为 PurchasingThe following example changes the default schema of the user Mary51 to Purchasing.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;  
GO  

另请参阅See also