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

# SHOW USERS

The `SHOW USERS` [statement](/docs/stable/sql-statements.md) lists the users for all databases.

> [!NOTE]
>
> Since the keywords `ROLES` and `USERS` can now be used interchangeably in SQL statements for enhanced PostgreSQL compatibility, `SHOW USERS` is now an alias for [`SHOW ROLES`](/docs/stable/show-roles).

## Synopsis

![show\_users syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/show_users.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=44d9b9ecb94db63413f07430f54ee1c2)

## Parameters

| Parameter | Description |
| - | - |
| `SOURCE = <string>` | Filter users by their provisioning source. Only users whose [`PROVISIONSRC`](/docs/stable/create-user#role-options) role option exactly matches the full string are returned. 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 excluded. |
| `LIMIT <n>` | Limit the number of rows returned. Can be used with or without `WITH` options. |

## Required privileges

The user must have the [`SELECT`](/docs/stable/select-clause) [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the `system.users` and `system.role_members` tables.

## Examples

### Show all users

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS;
```

```
  username |    options     | member_of | estimated_last_login_time
-----------+----------------+-----------+------------------------------
  admin    | {CREATEROLE}   | {}        | NULL
  carl     | {NOLOGIN}      | {}        | NULL
  petee    | {}             | {}        | 2025-08-04 19:18:00.201402+00
  root     | {CREATEROLE}   | {admin}   | NULL
(4 rows)
```

Alternatively, within the built-in SQL shell, you can use the `\du` [shell command](/docs/stable/cockroach-sql.md#commands):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> \du
```

```
  username |    options     | member_of | estimated_last_login_time
-----------+----------------+-----------+------------------------------
  admin    | {CREATEROLE}   | {}        | NULL
  carl     | {NOLOGIN}      | {}        | NULL
  petee    | {}             | {}        | 2025-08-04 19:18:00.201402+00
  root     | {CREATEROLE}   | {admin}   | NULL
(4 rows)
```

### Filter users by provisioning source

To find all users provisioned by a specific source, such as an [LDAP server](/docs/stable/ldap-authentication.md):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS WITH SOURCE = 'ldap:ldap.example.com';
```

```
  username   |                options                 | member_of | estimated_last_login_time
-------------+----------------------------------------+-----------+------------------------------
  prov_user1 | {PROVISIONSRC=ldap:ldap.example.com}   | {}        | 2025-07-01 10:30:00.000000+00
  prov_user2 | {PROVISIONSRC=ldap:ldap.example.com}   | {}        | 2025-06-15 08:45:00.000000+00
(2 rows)
```

The `SOURCE` filter matches against the [`PROVISIONSRC`](/docs/stable/create-user#role-options) role option, which is set automatically for users provisioned through [LDAP](/docs/stable/ldap-authentication.md), [OIDC](/docs/stable/sso-db-console.md), or [JWT](/docs/stable/sso-sql.md) authentication.

### Filter users by last login time

To find users who have not logged in since a specific date:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS WITH LAST LOGIN BEFORE '2026-01-01';
```

```
  username | options | member_of |   estimated_last_login_time
-----------+---------+-----------+--------------------------------
  petee    | {}      | {}        | 2025-08-04 19:18:00.201402+00
(1 row)
```

> [!NOTE]
>
> Users who have never logged in (`NULL` `estimated_last_login_time`) are excluded from `LAST LOGIN BEFORE` results.

### Combine filters with a limit

You can combine multiple `WITH` options (comma-separated) and add a `LIMIT` clause:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2025-06-01' LIMIT 100;
```

### Limit results

The `LIMIT` clause can also be used without `WITH` options:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW USERS LIMIT 5;
```

## See also

* [`CREATE USER`](/docs/stable/create-user)
* [`ALTER USER`](/docs/stable/alter-user)
* [`DROP PROVISIONED ROLES`](/docs/stable/drop-provisioned-roles)
* [LDAP Authentication](/docs/stable/ldap-authentication.md)
* [Manage Users](/docs/stable/security-reference/authorization.md#create-and-manage-users)