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

从当前数据库的 SQL ServerSQL Server 角色中删除安全帐户。Removes a security account from a SQL ServerSQL Server 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

SQL Server 和 Azure SQL 数据库的语法Syntax for both SQL Server and Azure SQL Database

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

适用于 Azure SQL 数据仓库和并行数据仓库的语法Syntax for both Azure SQL Data Warehouse and Parallel Data Warehouse

sp_droprolemember 'role' ,  
     'security_account'  

参数Arguments

[ @rolename = ] 'role'要从中删除成员的角色的名称。[ @rolename = ] 'role' Is the name of the role from which the member is being removed. role的值为sysname,无默认值。role is sysname, with no default. 角色必须存在于当前数据库中。role must exist in the current database.

[ @membername = ] 'security_account'要从角色中删除的安全帐户的名称。[ @membername = ] 'security_account' Is the name of the security account being removed from the role. security_account sysname,无默认值。security_account is sysname, with no default. security_account可以是数据库用户、其他数据库角色、windows 登录名或 windows 组。security_account can be a database user, another database role, a Windows login, or a Windows group. 当前数据库中必须存在security_accountsecurity_account must exist in the current database.

返回代码值Return Code Values

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

备注Remarks

sp_droprolemember 通过从 sysmembers 表中删除行来删除数据库角色的成员。sp_droprolemember removes a member from a database role by deleting a row from the sysmembers table. 在从角色中删除一个成员后,该成员将失去作为该角色的成员所拥有的任何权限。When a member is removed from a role the member loses any permissions it has by membership in that role.

若要删除固定服务器角色的用户,请使用 sp_dropsrvrolemember。To remove a user from a fixed server role, use sp_dropsrvrolemember. 不能删除 public 角色的用户,也不能从任何角色中删除 dbo。Users cannot be removed from the public role, and dbo cannot be removed from any role.

使用 sp_helpuser 查看SQL ServerSQL Server角色的成员,并使用 ALTER role 将成员添加到角色。Use sp_helpuser to see the members of a SQL ServerSQL Server role, and use ALTER ROLE to add a member to a role.

权限Permissions

要求具有角色的 ALTER 权限。Requires ALTER permission on the role.

示例Examples

以下示例将删除角色 JonB 中的用户 SalesThe following example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'Jonb';  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

以下示例将删除角色 JonB 中的用户 SalesThe following example removes the user JonB from the role Sales.

EXEC sp_droprolemember 'Sales', 'JonB'  

另请参阅See Also

安全存储过程 (Transact-sql) Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL) sp_addrolemember (Transact-SQL)
sp_droprole (Transact-sql) sp_droprole (Transact-SQL)
sp_dropsrvrolemember (Transact-sql) sp_dropsrvrolemember (Transact-SQL)
sp_helpuser (Transact-sql) sp_helpuser (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)