> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SHOW USERS

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `SHOW USERS` <InternalLink path="sql-statements">statement</InternalLink> 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 <InternalLink path="show-roles">`SHOW ROLES`</InternalLink>.
</Note>

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/G8d8_97yac-RRycp/images/sql-diagrams/v26.3/show_users.svg?fit=max&auto=format&n=G8d8_97yac-RRycp&q=85&s=1a06666818f25775208061774dd2bee5" alt="show_users syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="585" height="37" data-path="images/sql-diagrams/v26.3/show_users.svg" />

## Parameters

| Parameter                       | Description                                                                                                                                                                                                                                                                  |
| ------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `SOURCE = <string>`             | Filter users by their provisioning source. Only users whose <InternalLink path="create-user#role-options">`PROVISIONSRC`</InternalLink> 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 <InternalLink path="select-clause">`SELECT`</InternalLink> <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> 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` <InternalLink path="cockroach-sql#commands">shell command</InternalLink>:

```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 <InternalLink path="ldap-authentication">LDAP server</InternalLink>:

```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 <InternalLink path="create-user#role-options">`PROVISIONSRC`</InternalLink> role option, which is set automatically for users provisioned through <InternalLink path="ldap-authentication">LDAP</InternalLink>, <InternalLink path="sso-db-console">OIDC</InternalLink>, or <InternalLink path="sso-sql">JWT</InternalLink> 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.
</Note>

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

* <InternalLink path="create-user">`CREATE USER`</InternalLink>
* <InternalLink path="alter-user">`ALTER USER`</InternalLink>
* <InternalLink path="ldap-authentication">LDAP Authentication</InternalLink>
* <InternalLink path="security-reference/authorization#create-and-manage-users">Manage Users</InternalLink>