sp_droprole (T-SQL)

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

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 db_owner or db_securityadmin fixed database roles can execute sp_droprole.

Examples

This example removes the SQL Server role Sales.

EXEC sp_droprole 'Sales'

See Also