| title | sp_droprolemember (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/20/2017 | ||
| ms.prod | sql-non-specified | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | c2f19ab1-e742-4d56-ba8e-8ffd40cf4925 | ||
| caps.latest.revision | 39 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-all_md]
Removes a security account from a [!INCLUDEssNoVersion] role in the current database.
Important
[!INCLUDEssNoteDepFutureAvoid] Use ALTER ROLE instead.
Transact-SQL Syntax Conventions
-- Syntax for SQL Server and Azure SQL Database
sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
sp_droprolemember 'role' ,
'security_account'
[ @rolename = ] 'role'
Is the name of the role from which the member is being removed. role is sysname, with no default. role must exist in the current database.
[ @membername = ] 'security_account'
Is the name of the security account being removed from the role. security_account is sysname, with no default. security_account can be a database user, another database role, a Windows login, or a Windows group. security_account must exist in the current database.
0 (success) or 1 (failure)
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.
To remove a user from a fixed server role, use sp_dropsrvrolemember. Users cannot be removed from the public role, and dbo cannot be removed from any role.
Use sp_helpuser to see the members of a [!INCLUDEssNoVersion] role, and use ALTER ROLE to add a member to a role.
Requires ALTER permission on the role.
The following example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'Jonb';
The following example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'JonB'
Security Stored Procedures (Transact-SQL)
sp_addrolemember (Transact-SQL)
sp_droprole (Transact-SQL)
sp_dropsrvrolemember (Transact-SQL)
sp_helpuser (Transact-SQL)
System Stored Procedures (Transact-SQL)