Removes a security account from a SQL Server role in the current database.
This feature will 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. Use ALTER ROLE instead.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
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 ALTER ROLE to add a member to a role.
The following example removes the user JonB from the role Sales.
EXEC sp_droprolemember 'Sales', 'Jonb';