List users in Snowflake

Queries below returns list of users in current database.

Notes

Command below can only be executed by users with a role that has the MANAGE GRANTS global privilege. This privilege is usually only granted to the ACCOUNTADMIN and SECURITYADMIN roles.

Show statement

show users;

This statement return all users in Snowflake database with details.

Post-process

To post-process user information you can use result_scan() function. If you know query ID of show statement you can pass it as function argument. In example below we use last_query_id() to obtain last query ID of our session.

Query

Important: Query works only if is executed right after show users statement

select "name",
       "login_name",
       "default_role",
       "last_success_login",
       "expires_at_time",
from table(result_scan(last_query_id()))
order by "name";

Columns

  • name - user name
  • login_name - name used for loging to snowflake database
  • default_role - default role user has
  • last_success_login - datetime of last successed login
  • expires_at_time - datetime of account expiration

Rows

  • One row represents one user in the database
  • Scope of rows: all users in the database
  • Ordered by user name

Sample results

Those results show that there are 5 users in current database.

sample results

Snowflake Web Interface

If you are logged as user with privilages mentioned before you can list users in Snowlfake Web Interface. To do this select Account button at top of the page and then select Users tab.

snowUI

0
There are no comments. Click here to write the first comment.