sp_droprole
Removes a Microsoft® SQL Server™ role from the current database.
Syntax
sp_droprole [ @rolename = ] 'role'
Arguments
[@rolename =] 'role'
Is the name of the role to remove from the current database. role is sysname, with no default. role must already exist in the current database.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column name | Data type | Description |
|---|---|---|
| Name | sysname | The name of the existing member of the role. |
Remarks
Only standard user roles can be removed using sp_droprole. To remove an application role, use sp_dropapprole.
A role with existing members cannot be removed. All members of the role must first be removed from the role before the role can be removed. To remove users from a role, use sp_droprolemember. If any users are still members of the role, sp_droprole displays those members.
Fixed roles and the public role cannot be removed.
A role cannot be removed if it owns any objects. Either remove the objects before removing the role, or use sp_changeobjectowner to change the owner of any objects that must not be removed.
sp_droprole 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, or the owner of the role, can execute sp_droprole.
Examples
This example removes the SQL Server role Sales.
EXEC sp_droprole 'Sales'