DROP ROLE Statement

Syntax

DROP ROLE [ FROM USER ] role_name
[ WITH REVOKE ]
[ WITH DROP OBJECTS ]

Parameters

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 a regular user. The role_name must exist in the database.
WITH REVOKE – required when dropping a standalone or user-extended role to which users have been granted the underlying system privileges of the role with either the WITH ADMIN OPTION or WITH NO ADMIN OPTION clause.
WITH DROP OBJECTS – required when dropping a standalone or user-extended role that owns objects.

Examples

Example 1 – converts a user-extended role named Joe that does not have its underlying system privileges granted to any user back to a regular user.
```
DROP ROLE FROM USER Joe
```
Example 2 – drops a user-extended role named Jack that does not have its underlying system privileges granted to any user from the database.
```
DROP ROLE Jack
```
Example 3 – converts a user-extended role named Sam that has underlying system privileges granted to users back to a regular role.
```
DROP ROLE FROM USER Sam
WITH REVOKE
```
Example 4 – drops a standalone role named Sales1 that does not own objects, nor has its underlying system privileges granted to any user from the database.
```
DROP ROLE Sales1
```
Example 5 – drops a standalone role named Sales2 that does not own objects, and has its underlying system privileges granted to users from the database.
```
DROP ROLE Sales2
WITH REVOKE
```
Example 6 – converts a user-extended role named Marketing1 that owns objects, but does not have its underlying system privileges granted to users back to a regular user.
```
DROP ROLE FROM USER Marketing1
WITH DROP OBJECTS
```
Example 7 – drops a standalone role named Marketing2 that owns objects, and has its underlying system privileges granted to users from the database.
```
DROP ROLE Marketing1
WITH REVOKE WITH DROP OBJECTS
```

Usage

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.

Include the FROM USER clause when dropping a user-extended role to convert it back to act as a regular user rather than remove it from 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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.