SHOW USERS¶
Lists all users in the system.
- See also:
Syntax¶
SHOW [ TERSE ] USERS
[ LIKE '<pattern>' ]
[ STARTS WITH '<name_string>' ]
[ LIMIT <rows> [ FROM '<name_string>' ] ]
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'
andSTARTS 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 bySTARTS 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 of the user. |
|
Date and time when the user was created. |
|
Name that the user enters to log into the system. |
|
Name displayed for the user in Snowsight. |
|
First name of the user. |
|
Last name of the user. |
|
Email addresss for the user. |
|
Number of minutes until the temporary lock on the user login is cleared. |
|
Number of days after which the user status is set to “Expired” and the user is no longer allowed to log in. |
|
Comment about the user. |
|
If |
|
If |
|
If |
|
Virtual warehouse that is active by default for the user’s session upon logging in. |
|
Namespace (database only or database and schema) that is active by default for the user’s session upon logging in. |
|
Primary role that is active by default for the user’s session upon logging in. |
|
Set of secondary roles that are active for the user’s session upon logging in. |
|
If |
|
Authorization ID used for Duo. |
|
Number of minutes to temporarily bypass MFA requirement for the user. |
|
Role that owns the user. |
|
Date and time when the user last logged in to the Snowflake. |
|
Date and time when the user’s status is set to |
|
Number of minutes until the temporary lock on the user login is cleared. |
|
If |
|
If |
|
Type of the user. For a list of possible values, see Types of users. |
|
If |
|
If |
|
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 namedmy_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;
+--------------+-------------------------------+---------------+--------------+------------+-----------+------------------------+----------------+----------------+---------+----------+----------------------+----------------+-------------------+-------------------+--------------+-------------------------+---------------+---------------+--------------------+--------------+-------------------------------+-----------------+-------------------+--------------+--------------------+--------+---------+---------+---------------------------------------+
| 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 |
+--------------+-------------------------------+---------------+--------------+------------+-----------+------------------------+----------------+----------------+---------+----------+----------------------+----------------+-------------------+-------------------+--------------+-------------------------+---------------+---------------+--------------------+--------------+-------------------------------+-----------------+-------------------+--------------+--------------------+--------+---------+---------+---------------------------------------+