DROP ROLE Statement

Quick Links:

Syntax

DROP ROLE [ FROM USER ] role_name
   [ WITH REVOKE ]

Parameters

(back to top)

role_name – must be the name of a role that already exists in the database.

FROM USER – required to convert a user-extended role back to act as a regular user rather than remove it from the database. The role_name must exist in the database.
The user retains any login privileges, system privileges, and roles granted to the user-extended role and becomes the owner of any objects owned by the user-extended role. Any users granted to the user-extended are immediately revoked.

WITH REVOKE – required when dropping a standalone or user-extended role to which users have been granted the underlying system privileges of the role. The grant can have been made with either the WITH ADMIN OPTION or WITH NO ADMIN OPTION clause.

Examples

(back to top)

Example 1 – converts a user-extended role named Joe that has not been granted to other users or roles back to a regular user:
```
DROP ROLE FROM USER Joe
```
Example 2 – drops a user-extended role named Jack that has not been granted to other users or roles from the database:
```
DROP ROLE Jack
```
Example 3 – converts a user-extended role named Sam that has been granted to other user or roles back to a regular role:
```
DROP ROLE FROM USER Sam
WITH REVOKE
```
Example 4 – drops a standalone role named Sales2 that has been granted to other users or roles from the database:
```
DROP ROLE Sales2
WITH REVOKE
```

Usage

(back to top)

A user-defined role can be dropped from the database or converted back to a regular user at any time as long as all dependent roles left meet the minimum required number of administrative users with active passwords.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL extension.

Permissions

(back to top)

Requires administrative rights over the role being dropped.
If the role being dropped owns objects, none are in use by any user in any session at the time the DROP statement is executed.