> ## 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

The `DROP PROVISIONED ROLES` [statement](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) bulk-drops [auto-provisioned](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md#option-1-automatic-user-provisioning-recommended) 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](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md), [OIDC](https://docs.cockroachlabs.com/docs/stable/sso-db-console.md), or [JWT](https://docs.cockroachlabs.com/docs/stable/sso-sql.md) authentication.

Only users that have a [`PROVISIONSRC`](https://docs.cockroachlabs.com/docs/stable/create-user#role-options) role option (set automatically during provisioning) are candidates for removal. Users created with [`CREATE USER`](https://docs.cockroachlabs.com/docs/stable/create-user) or [`CREATE ROLE`](https://docs.cockroachlabs.com/docs/stable/create-role) are never affected, and the [`DROP ROLE`](https://docs.cockroachlabs.com/docs/stable/drop-role) and [`DROP USER`](https://docs.cockroachlabs.com/docs/stable/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](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#object-ownership) (such as databases, tables, schemas, or types) or have other dependencies (direct privilege grants, [system privileges](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#supported-privileges), [default privileges](https://docs.cockroachlabs.com/docs/stable/alter-default-privileges.md), [row-level security](https://docs.cockroachlabs.com/docs/stable/row-level-security.md) 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`](https://docs.cockroachlabs.com/docs/stable/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](https://docs.cockroachlabs.com/docs/stable/ui-overview.md#authentication) [sessions](https://docs.cockroachlabs.com/docs/stable/cockroach-auth-session.md) are revoked.
* Each dropped user is recorded in the [`USER_ADMIN`](https://docs.cockroachlabs.com/docs/stable/logging#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`](https://docs.cockroachlabs.com/docs/stable/create-role#create-a-role-that-can-create-other-roles-and-manage-authentication-methods-for-the-new-roles) role option.

## Synopsis

![drop\_provisioned\_roles syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_provisioned_roles.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=d3656734fb405ccb3e972827f756c922)

## Parameters

| Parameter | Description |
| - | - |
| `SOURCE = <string>` | Filter users by their provisioning source. Only users whose [`PROVISIONSRC`](https://docs.cockroachlabs.com/docs/stable/create-user#role-options) role option exactly matches the full string are dropped. For example, `'ldap:ldap.example.com'`. Pattern matching is not supported. |
| `LAST LOGIN BEFORE <timestamp>` | Filter users whose `estimated_last_login_time` is before the specified timestamp. Users who have never logged in (`NULL` value) are also dropped. |
| `LIMIT <n>` | **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`](https://docs.cockroachlabs.com/docs/stable/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](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md) 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;
```

> [!NOTE]
>
> 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](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md)
* [`SHOW USERS`](https://docs.cockroachlabs.com/docs/stable/show-users)
* [`SHOW ROLES`](https://docs.cockroachlabs.com/docs/stable/show-roles)
* [`DROP ROLE`](https://docs.cockroachlabs.com/docs/stable/drop-role)
* [`DROP USER`](https://docs.cockroachlabs.com/docs/stable/drop-user)
* [`CREATE USER`](https://docs.cockroachlabs.com/docs/stable/create-user)
* [Manage Users](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#create-and-manage-users)
* [SQL Statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md)