Removes a user-defined role from the database or converts a
user-extended role to a regular user.
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:
- Example 2 drops a user-extended role named Jack that has not been granted to other users
or roles from the database:
- 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.