drop role

Description

Drops a user-defined role.

Syntax

drop role role_name [with override]

Parameters

role_name

is the name of the role you want to drop.

with override

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.

Examples

Example 1

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

Example 2

Drops the named role and removes permission information and any other reference to the role from all databases:

drop role doctor_role with override

Usage


Restrictions

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be a System Security Officer to use drop role.

drop role permission is not included in the grant all command.

Auditing

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

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter role, create role, grant, revoke, set

System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect, sp_modifylogin