sp_droprolemember
Removes a security account from a Microsoft® SQL Server™ role in the current database.
Syntax
sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
Arguments
'role'
Is the name of the role that the member is being removed from. role is sysname, with no default. role must exist in the current database.
'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 SQL Server user or another SQL Server role, or a Microsoft Windows NT® user or group. security_account must exist in the current database. When specifying a Windows NT user or group, specify the name that the Windows NT user or group is known by in the database (added using sp_grantdbaccess).
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_droprolemember removes a role member by deleting a row from the sysmembers table. When removing a member from a role, the permissions applied to the role are no longer applied to the former member of the role.
sp_droprolemember cannot be used to remove a Windows NT user from a Windows NT group; this must be done in the Windows NT security system. 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 from within a user-defined transaction.
Permissions
Only members of the sysadmin fixed server role, the db_owner and db_securityadmin fixed database roles can execute sp_droprolemember. Only a member of the db_owner fixed database role can remove users from a fixed database role.
Examples
This example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'Jonb'