sp_droprole

Transact-SQL Reference

Transact-SQL Reference

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'

See Also

sp_addrole

sp_dropapprole

System Stored Procedures