sp_droprolemember (Transact-SQL)
Removes a security account from a SQL Server role in the current database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
Arguments
- [ @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.
Return Code Values
0 (success) or 1 (failure)
Remarks
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 SQL Server role, and use sp_addrolemember to add a member to a role.
sp_droprolemember cannot be executed within a user-defined transaction.
Permissions
Requires ALTER permission on the role.
Examples
The following example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'Jonb'
See Also
Reference
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)