Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize


SQL Server 2000

Removes a Microsoft® SQL Server™ role from the current database.


sp_droprole [ @rolename = ] 'role'


[@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.


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.


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.


This example removes the SQL Server role Sales.

EXEC sp_droprole 'Sales'

See Also



System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft