Categories:

User & Security DDL (Roles)

DROP ROLE

Removes the specified role from the system.

Syntax

DROP ROLE [ IF EXISTS ] <name>

Parameters

name

Specifies the identifier for the role to drop. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

Usage Notes

  • Dropped roles cannot be recovered; they must be recreated.

  • Ownership of any objects owned by the dropped role are transferred to the role that executes the DROP ROLE command.

  • If a role has a future privilege as a grantor or grantee, the role can only be dropped by a user with a role that has the MANAGE GRANTS privilege.

  • All current and future grants where the role is grantor/grantee are removed when the role is dropped.

Tip

Before dropping a role, to retain current grants, consider transfering ownership using the following syntax:

GRANT OWNERSHIP
    ON { objectType <object_name> | ALL schemaObjectsType IN SCHEMA <schema_name> }
    TO ROLE <name>
    COPY CURRENT GRANTS

For more details, see GRANT OWNERSHIP.

Examples

DROP ROLE myrole;