User & Security DDL (Roles)
Removes the specified role from the system.
DROP ROLE [ IF EXISTS ] <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.
Dropped roles cannot be recovered; they must be recreated.
Ownership of any objects owned by the dropped role is transferred to the role that executes the DROP ROLE command. To transfer ownership of each of these objects to a different role, use GRANT OWNERSHIP … COPY CURRENT GRANTS.
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 that name the role as either the grantor or the grantee are removed when the role is dropped.
Query the GRANTS_TO_ROLES Account Usage view to retrieve the privilege grants that name a specified role as the grantor or grantee:
SELECT * FROM snowflake.account_usage.grants_to_roles WHERE grantee_name = upper('<role_name>') OR granted_by = upper('<role_name>');
The following example retrieves the grants where
myroleis the grantor or grantee:
SELECT * FROM snowflake.account_usage.grants_to_roles WHERE grantee_name = upper('myrole') OR granted_by = upper('myrole');
If a role is a grantor of roles to users, dropping the role revokes these grants automatically.
DROP ROLE myrole;