Removes a security account from a Microsoft® SQL Server™ role in the current database.
sp_droprolemember [ @rolename = ] 'role' ,
[ @membername = ] 'security_account'
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.
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)
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.
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.
This example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'Jonb'