Export (0) Print
Expand All
Expand Minimize

sp_droprolemember (Transact-SQL)

Removes a security account from a SQL Server role in the current database.

Important note Important

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.

Topic link icon Transact-SQL Syntax Conventions

sp_droprolemember [ @rolename = ] 'role' , 
     [ @membername = ] '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 SQL Server 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';

Community Additions

ADD
Show:
© 2014 Microsoft