SHOW USERS

Lists all users in the system.

See also:

CREATE USER , ALTER USER , DROP USER , DESCRIBE USER

Syntax

SHOW [ TERSE ] USERS
  [ LIKE '<pattern>' ]
  [ STARTS WITH '<name_string>' ]
  [ LIMIT <rows> [ FROM '<name_string>' ] ]
Copy

Parameters

TERSE

Returns only the following output columns:

  • name

  • created_on

  • display_name

  • first_name

  • last_name

  • email

  • org_identity

  • comment

  • has_password

  • has_rsa_public_key

  • type

  • has_mfa

  • has_pat

  • has_federated_workload_authentication

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...
... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

STARTS WITH 'name_string'

Optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case sensitive.

For example, the following strings return different results:

... STARTS WITH 'B' ...
... STARTS WITH 'b' ...

. Default: No value (no filtering is applied to the output)

LIMIT rows [ FROM 'name_string' ]

Optionally limits the maximum number of rows returned, while also enabling “pagination” of the results. The actual number of rows returned might be less than the specified limit. For example, the number of existing objects is less than the specified limit.

The optional FROM 'name_string' subclause effectively serves as a “cursor” for the results. This enables fetching the specified number of rows following the first row whose object name matches the specified string:

  • The string must be enclosed in single quotes and is case sensitive.

  • The string does not have to include the full object name; partial names are supported.

Default: No value (no limit is applied to the output)

Note

For SHOW commands that support both the FROM 'name_string' and STARTS WITH 'name_string' clauses, you can combine both of these clauses in the same statement. However, both conditions must be met or they cancel out each other and no results are returned.

In addition, objects are returned in lexicographic order by name, so FROM 'name_string' only returns rows with a higher lexicographic value than the rows returned by STARTS WITH 'name_string'.

For example:

  • ... STARTS WITH 'A' LIMIT ... FROM 'B' would return no results.

  • ... STARTS WITH 'B' LIMIT ... FROM 'A' would return no results.

  • ... STARTS WITH 'A' LIMIT ... FROM 'AB' would return results (if any rows match the input strings).

Output

The output of the command includes the following columns, which describe the properties and metadata of the object:

Column

Description

name

Name of the user.

created_on

Date and time when the user was created.

login_name

Name that the user enters to log into the system.

display_name

Name displayed for the user in Snowsight.

first_name

First name of the user.

last_name

Last name of the user.

email

Email addresss for the user.

mins_to_unlock

Number of minutes until the temporary lock on the user login is cleared.

days_to_expiry

Number of days after which the user status is set to “Expired” and the user is no longer allowed to log in.

comment

Comment about the user.

disabled

If true, the user is locked out of Snowflake and cannot log back in.

must_change_password

If true, the user is forced to change their password on next login (including their first/initial login) into the system.

snowflake_lock

If true, the user is locked by Snowflake. When a user is locked, they are unable to log in until the lock is removed.

default_warehouse

Virtual warehouse that is active by default for the user’s session upon logging in.

default_namespace

Namespace (database only or database and schema) that is active by default for the user’s session upon logging in.

default_role

Primary role that is active by default for the user’s session upon logging in.

default_secondary_roles

Set of secondary roles that are active for the user’s session upon logging in.

ext_authn_duo

If true, Duo is enabled for the user, which requires the user to use MFA (multi-factor authentication) when logging in.

ext_authn_uid

Authorization ID used for Duo.

mins_to_bypass_mfa

Number of minutes to temporarily bypass MFA requirement for the user.

owner

Role that owns the user.

last_success_login

Date and time when the user last logged in to the Snowflake.

expires_at_time

Date and time when the user’s status is set to EXPIRED and the user can no longer log in.

locked_until_time

Number of minutes until the temporary lock on the user login is cleared.

has_password

If true, the user has a password.

has_rsa_public_key

If true, the user has a public key for key-pair authentication.

type

Type of the user. For a list of possible values, see Types of users.

has_mfa

If true, the user is enrolled in multi-factor authentication (MFA).

has_pat

If true, the user has one or more programmatic access tokens.

has_federated_workload_authentication

Reserved for future use.

Access control requirements

Any user can execute the SHOW USERS command. The output always includes the username in the name column.

For the other columns, Snowflake filters the output based upon the privileges granted to the user’s active role. The values in the other columns are returned if the active role has either of the following privileges:

Privilege

Object

Notes

OWNERSHIP

User

MANAGE GRANTS

Account

Otherwise, the other columns contain NULL.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes

  • If the account has more than 10,000 users, you can use the LIMIT … FROM … parameter to return smaller sets of users.

    For example, you can run SHOW USERS LIMIT 10000 FROM my_user to return the next 10000 users starting from the user named my_user.

  • The command doesn’t require a running warehouse to execute.

  • The command only returns objects for which the current user’s current role has been granted at least one access privilege.

  • The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.

  • To post-process the output of this command, you can use the pipe operator or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.

Examples

The following example lists the users in the account:

SHOW USERS;
Copy
+--------------+-------------------------------+---------------+--------------+------------+-----------+------------------------+----------------+----------------+---------+----------+----------------------+----------------+-------------------+-------------------+--------------+-------------------------+---------------+---------------+--------------------+--------------+-------------------------------+-----------------+-------------------+--------------+--------------------+--------+---------+---------+---------------------------------------+
| name         | created_on                    | login_name    | display_name | first_name | last_name | email                  | mins_to_unlock | days_to_expiry | comment | disabled | must_change_password | snowflake_lock | default_warehouse | default_namespace | default_role | default_secondary_roles | ext_authn_duo | ext_authn_uid | mins_to_bypass_mfa | owner        | last_success_login            | expires_at_time | locked_until_time | has_password | has_rsa_public_key | type   | has_mfa | has_pat | has_federated_workload_authentication |
|--------------+-------------------------------+---------------+------------- +------------+-----------+------------------------+----------------+----------------+---------+----------+----------------------+----------------+-------------------+-------------------+--------------+-------------------------+---------------+---------------+--------------------+--------------+-------------------------------+-----------------+-------------------+--------------+--------------------+--------+---------+---------+---------------------------------------|
| MY_USER_NAME | 2020-04-28 12:24:38.722 -0700 | MY_LOGIN_NAME | Jane Smith   | Jane       | Smith     | jane.smith@example.com | NULL           | NULL           | NULL    | false    | false                | false          | MY_WAREHOUSE      | MY_DB.MY_SCHEMA   | MY_ROLE      | []                      | false         | NULL          | NULL               | ACCOUNTADMIN | 2025-06-12 15:02:22.783 -0700 | NULL            | NULL              | true         | true               | PERSON | true    | true    | false                                 |
+--------------+-------------------------------+---------------+--------------+------------+-----------+------------------------+----------------+----------------+---------+----------+----------------------+----------------+-------------------+-------------------+--------------+-------------------------+---------------+---------------+--------------------+--------------+-------------------------------+-----------------+-------------------+--------------+--------------------+--------+---------+---------+---------------------------------------+