Skip to main content
The DROP PROVISIONED ROLES bulk-drops SQL users that match a set of filter criteria. It is designed to simplify de-provisioning of dormant or removed users that were automatically created through , , or authentication. Only users that have a role option (set automatically during provisioning) are candidates for removal. Users created with or are never affected, and the and statements are unchanged by this statement.

​
Considerations

  • The LIMIT clause is required. It must be a constant integer between 1 and 1024. This is a safety guard that prevents accidentally dropping an unbounded number of provisioned users in a single transaction. To remove more provisioned users than the maximum, run the statement repeatedly.
  • Only provisioned users (users with a PROVISIONSRC role option) are dropped. Non-provisioned users are never affected, even if they match the filter criteria.
  • The root and admin users are never dropped.
  • Provisioned users that (such as databases, tables, schemas, or types) or have other dependencies (direct privilege grants, , , policies, or ownership of scheduled jobs) are skipped rather than dropped. No error is returned for skipped users. Transfer ownership or revoke privileges before dropping these users.
  • If the user running the statement is not a member of the admin role, any provisioned users that are members of admin are skipped. (This differs from , which returns an error when a non-admin attempts to drop an admin user.)
  • When a provisioned user is dropped, its role memberships are removed and any active are revoked.
  • Each dropped user is recorded in the logging channel, which you can use for auditing de-provisioning operations.

​
Required privileges

To drop provisioned roles, the user must be a member of the admin role or have the role option.

​
Synopsis

drop_provisioned_roles syntax diagram

​
Parameters

When you specify more than one WITH option, separate the options with commas. Only users matching all specified options are dropped.

​
Examples

The following examples assume that the user running each statement has the admin role or the CREATEROLE role option. Use with the same filter clauses to preview which users would be dropped before running DROP PROVISIONED ROLES.

​
Drop provisioned users from a specific source

To drop up to 100 users that were provisioned by a specific server:

​
Drop dormant provisioned users

To drop up to 100 provisioned users that have not logged in since a specific date:
Users who have never logged in (NULL estimated_last_login_time) are also dropped by LAST LOGIN BEFORE.

​
Combine filters

To drop up to 100 users that were provisioned by a specific source and have not logged in since a specific date:

​
See also