ALTER ROLE (Transact-SQL)ALTER ROLE (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

为数据库角色添加或删除成员,或更改用户定义的数据库角色的名称。Adds or removes members to or from a database role, or changes the name of a user-defined database role.

备注

若要更改在 SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 中添加或删除成员的角色,请使用 sp_addrolemember (Transact-SQL)sp_droprolemember (Transact-SQL)To alter roles adding or dropping members in SQL 数据仓库SQL Data Warehouse or 并行数据仓库Parallel Data Warehouse, use sp_addrolemember (Transact-SQL) and sp_droprolemember (Transact-SQL).

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server (starting with 2012) and Azure SQL Database  
  
ALTER ROLE  role_name  
{  
       ADD MEMBER database_principal  
    |  DROP MEMBER database_principal  
    |  WITH NAME = new_name  
}  
[;]  
-- Syntax for SQL Server 2008, Azure SQL Data Warehouse and Parallel Data Warehouse
  
-- Change the name of a user-defined database role  
ALTER ROLE role_name   
    WITH NAME = new_name  
[;]  

参数Arguments

role_name RoleName
适用对象:SQL ServerSQL Server(从 2008 开始)、Azure SQL 数据库Azure SQL Database APPLIES TO: (starting with 2008),

指定要更改的数据库角色。Specifies the database role to change.

ADD MEMBER database_principal ADD MEMBER database_principal
适用对象:SQL ServerSQL Server(从 2012 开始)、Azure SQL 数据库Azure SQL Database APPLIES TO: (starting with 2012),

指定向数据库角色的成员身份添加数据库主体。Specifies to add the database principal to the membership of a database role.

  • database_principal 是数据库用户或用户定义的数据库角色 。 database_principal is a database user or a user-defined database role.

  • database_principal 不能是固定的数据库角色或是服务器主体 。 database_principal cannot be a fixed database role or a server principal.

DROP MEMBER database_principal DROP MEMBER database_principal
适用对象:SQL ServerSQL Server(从 2012 开始)、Azure SQL 数据库Azure SQL Database APPLIES TO: (starting with 2012),

指定从数据库角色的成员身份删除数据库主体。Specifies to remove a database principal from the membership of a database role.

  • database_principal 是数据库用户或用户定义的数据库角色 。 database_principal is a database user or a user-defined database role.

  • database_principal 不能是固定的数据库角色或是服务器主体 。 database_principal cannot be a fixed database role or a server principal.

WITH NAME = new_name WITH NAME = new_name
适用对象:SQL ServerSQL Server(从 2008 开始)、Azure SQL 数据库Azure SQL Database APPLIES TO: (starting with 2008),

指定更改用户定义的数据库角色的名称。Specifies to change the name of a user-defined database role. 数据库中必须尚未包含新名称。The new name must not already exist in the database.

更改数据库角色的名称不会更改角色的 ID 号、所有者或权限。Changing the name of a database role does not change ID number, owner, or permissions of the role.

权限Permissions

需具有以下一项或多项权限或成员身份才能运行此命令:To run this command you need one or more of these permissions or memberships:

  • 对角色具有 ALTER 权限 ALTER permission on the role
  • 对数据库具有 ALTER ANY ROLE 权限 ALTER ANY ROLE permission on the database
  • 具有 db_securityadmin 固定数据库角色的成员身份 Membership in the db_securityadmin fixed database role

此外,若要更改固定数据库角色中的成员身份还需要:Additionally, to change the membership in a fixed database role you need:

  • 具有 db_owner 固定数据库角色的成员身份 Membership in the db_owner fixed database role

限制和局限Limitations and restrictions

不能更改固定数据库角色的名称。You cannot change the name of a fixed database role.

元数据Metadata

这些系统视图中包含有关数据库角色和数据库主体的信息。These system views contain information about database roles and database principals.

示例Examples

A.A. 更改数据库角色的名称Change the name of a database role

适用对象:SQL ServerSQL Server(从 2008 开始)、SQL 数据库SQL Database APPLIES TO: (starting with 2008),

以下示例将角色 buyers 的名称更改为 purchasingThe following example changes the name of role buyers to purchasing. 本示例可以在 AdventureWorks 示例数据库中执行。This example can be executed in the AdventureWorks sample database.

ALTER ROLE buyers WITH NAME = purchasing;  

B.B. 添加或删除角色成员Add or remove role members

适用对象:SQL ServerSQL Server(从 2012 开始)、SQL 数据库SQL Database APPLIES TO: (starting with 2012),

此示例创建一个名为 Sales 的数据库角色。This example creates a database role named Sales. 示例向成员身份添加一个名为 Barry 的数据库用户,然后演示如何删除成员 Barry。It adds a database user named Barry to the membership, and then shows how to remove the member Barry. 本示例可以在 AdventureWorks 示例数据库中执行。This example can be executed in the AdventureWorks sample database.

CREATE ROLE Sales;  
ALTER ROLE Sales ADD MEMBER Barry;  
ALTER ROLE Sales DROP MEMBER Barry;  

另请参阅See Also

CREATE ROLE (Transact-SQL) CREATE ROLE (Transact-SQL)
主体(数据库引擎) Principals (Database Engine)
DROP ROLE (Transact-SQL) DROP ROLE (Transact-SQL)
sp_addrolemember (Transact-SQL) sp_addrolemember (Transact-SQL)
sys.database_role_members (Transact-SQL) sys.database_role_members (Transact-SQL)
sys.database_principals (Transact-SQL) sys.database_principals (Transact-SQL)