sp_addrolemember (Transact-SQL)sp_addrolemember (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

为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows 登录名或 Windows 组。Adds a database user, database role, Windows login, or Windows group to a database role in the current database.

重要

此功能处于维护模式并且可能会在 Microsoft SQL Server 将来的版本中被删除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.请改用ALTER ROLEUse ALTER ROLE instead.

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

语法Syntax

sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'  

参数Arguments

[ @rolename= ]"role"[ @rolename= ] 'role'
当前数据库中的数据库角色的名称。Is the name of the database role in the current database. rolesysname,无默认值。role is a sysname, with no default.

[ @membername= ]"security_account"[ @membername= ] 'security_account'
添加到该角色中的安全帐户。Is the security account being added to the role. security_accountsysname,无默认值。security_account is a sysname, with no default. security_account可以是数据库用户、数据库角色、windows 登录名或 windows 组。security_account can be a database user, database role, Windows login, or Windows group.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

使用 sp_addrolemember 添加到角色中的成员会继承该角色的权限。A member added to a role by using sp_addrolemember inherits the permissions of the role. 如果新成员是没有对应数据库用户的 Windows 级主体,则会创建数据库用户,但数据库用户可能不会完全映射到登录名。If the new member is a Windows-level principal without a corresponding database user, a database user will be created but may not be fully mapped to the login. 始终应检查登录名是否存在以及是否能访问数据库。Always check that the login exists and has access to the database.

角色不能将自身包含为成员。A role cannot include itself as a member. 即使只有一个或多个中间成员身份间接体现这种成员关系,这种“循环”定义也无效。Such "circular" definitions are not valid, even when membership is only indirectly implied by one or more intermediate memberships.

sp_addrolemember 无法将固定数据库角色、固定服务器角色或 dbo 添加到角色。sp_addrolemember cannot add a fixed database role, fixed server role, or dbo to a role.

只能使用 sp_addrolemember 将向数据库角色添加成员。Only use sp_addrolemember to add a member to a database role. 若要将成员添加到服务器角色,请使用(transact-sql)sp_addsrvrolemember To add a member to a server role, use sp_addsrvrolemember (Transact-SQL).

权限Permissions

为灵活的数据库角色添加成员需要满足以下条件之一:Adding members to flexible database roles requires one of the following:

  • Db_securityadmin 或 db_owner 固定数据库角色的成员身份。Membership in the db_securityadmin or db_owner fixed database role.

  • 具有拥有该角色的角色的成员身份。Membership in the role that owns the role.

  • 对角色具有ALTER ANY role权限或alter权限。ALTER ANY ROLE permission or ALTER permission on the role.

向固定数据库角色添加成员要求具有 db_owner 固定数据库角色的成员身份。Adding members to fixed database roles requires membership in the db_owner fixed database role.

示例Examples

A.A. 添加 Windows 登录名Adding a Windows login

下面的示例将 Windows 登录名Contoso\Mary5作为用户AdventureWorks2012 Mary5添加到数据库中。The following example adds the Windows login Contoso\Mary5 to the AdventureWorks2012 database as user Mary5. 用户 Mary5 随即被添加到 Production 角色中。The user Mary5 is then added to the Production role.

备注

因为 Contoso\Mary5Mary5 数据库中被识别为数据库用户 AdventureWorks2012AdventureWorks2012,所以必须指定用户名 Mary5Because Contoso\Mary5 is known as the database user Mary5 in the AdventureWorks2012AdventureWorks2012 database, the user name Mary5 must be specified. 如果没有 Contoso\Mary5 登录名存在,语句将失败。The statement will fail unless a Contoso\Mary5 login exists. 请通过使用您的域中的登录名进行测试。Test by using a login from your domain.

USE AdventureWorks2012;  
GO  
CREATE USER Mary5 FOR LOGIN [Contoso\Mary5] ;  
GO  

B.B. 添加数据库用户Adding a database user

以下示例将数据库用户 Mary5 添加到当前数据库的 Production 数据库角色中。The following example adds the database user Mary5 to the Production database role in the current database.

EXEC sp_addrolemember 'Production', 'Mary5';  

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

C.C. 添加 Windows 登录名Adding a Windows login

下面的示例将登录名LoginMary作为用户AdventureWorks2008R2 UserMary添加到数据库中。The following example adds the login LoginMary to the AdventureWorks2008R2 database as user UserMary. 用户 UserMary 随即被添加到 Production 角色中。The user UserMary is then added to the Production role.

备注

因为登录名LoginMary称为数据库中UserMary AdventureWorks2012AdventureWorks2012的数据库用户,所以UserMary必须指定用户名。Because the login LoginMary is known as the database user UserMary in the AdventureWorks2012AdventureWorks2012 database, the user name UserMary must be specified. 如果没有 Mary5 登录名存在,语句将失败。The statement will fail unless a Mary5 login exists. 登录名和用户通常具有相同的名称。Logins and users usually have the same name. 此示例使用不同的名称来区分影响登录名和用户的操作。This example uses different names to differentiate the actions affecting the login vs. the user.

-- Uses AdventureWorks  
  
CREATE USER UserMary FOR LOGIN LoginMary ;  
GO  
EXEC sp_addrolemember 'Production', 'UserMary'  

D.D. 添加数据库用户Adding a database user

以下示例将数据库用户 UserMary 添加到当前数据库的 Production 数据库角色中。The following example adds the database user UserMary to the Production database role in the current database.

EXEC sp_addrolemember 'Production', 'UserMary'  

另请参阅See Also

安全存储过程 (Transact-sql) Security Stored Procedures (Transact-SQL)
sp_addsrvrolemember (Transact-sql) sp_addsrvrolemember (Transact-SQL)
sp_droprolemember (Transact-sql) sp_droprolemember (Transact-SQL)
sp_grantdbaccess (Transact-sql) sp_grantdbaccess (Transact-SQL)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
数据库级角色Database-Level Roles