> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.
# DROP PROVISIONED ROLES
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `DROP PROVISIONED ROLES` statement bulk-drops auto-provisioned 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 LDAP, OIDC, or JWT authentication.
Only users that have a `PROVISIONSRC` role option (set automatically during provisioning) are candidates for removal. Users created with `CREATE USER` or `CREATE ROLE` are never affected, and the `DROP ROLE` and `DROP USER` 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 own objects (such as databases, tables, schemas, or types) or have other dependencies (direct privilege grants, system privileges, default privileges, row-level security 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 `DROP ROLE`, 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 web sessions are revoked.
* Each dropped user is recorded in the `USER_ADMIN` 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 `CREATEROLE` role option.
## Synopsis
## Parameters
| Parameter | Description |
| ------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `SOURCE = ` | Filter users by their provisioning source. Only users whose `PROVISIONSRC` role option exactly matches the full string are dropped. For example, `'ldap:ldap.example.com'`. Pattern matching is not supported. |
| `LAST LOGIN BEFORE ` | Filter users whose `estimated_last_login_time` is before the specified timestamp. Users who have never logged in (`NULL` value) are also dropped. |
| `LIMIT ` | **Required.** The maximum number of provisioned users to drop, as a constant integer between `1` and `1024`. |
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 `SHOW USERS` 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 LDAP server:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP PROVISIONED ROLES WITH SOURCE = 'ldap:ldap.example.com' LIMIT 100;
```
### Drop dormant provisioned users
To drop up to 100 provisioned users that have not logged in since a specific date:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP PROVISIONED ROLES WITH LAST LOGIN BEFORE '2025-01-01' LIMIT 100;
```
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP PROVISIONED ROLES WITH SOURCE = 'oidc:okta.corp.com', LAST LOGIN BEFORE '2025-01-01' LIMIT 100;
```
## See also
* LDAP Authentication
* `SHOW USERS`
* `SHOW ROLES`
* `DROP ROLE`
* `DROP USER`
* `CREATE USER`
* Manage Users
* SQL Statements