Drops a user-defined role.
drop role role_name [with override]
is the name of the role you want to drop.
overrides any restrictions on dropping a role. When you use the with override option, you can drop any role without having to check whether the role permissions have been dropped in each database.
Drops the named role only if all permissions in all databases have been revoked. The system administrator or object owner must revoke permissions granted in each database before dropping a role, or the command fails:
drop role doctor_role
Drops the named role and removes permission information and any other reference to the role from all databases:
drop role doctor_role with override
You need not drop memberships before dropping a role. Dropping a role automatically removes any user’s membership in that role, regardless of whether you use the with override option.
Use drop role from the master database.
You cannot use drop role to drop system roles.
ANSI SQL – Compliance level: Transact-SQL extension.
The permission checks for drop role differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage roles privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sso_role. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
85 |
roles |
create role, drop role, alter role, grant role, or revoke role |
|
Commands alter role, create role, grant, revoke, set
System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect,
