CODEWITHSUNDEEP
snowflake-cloud-data-platform
Stephen Yorke
Stephen Yorke

Reputation: 307

Is there a query that could show the user status of accountadmin grants?

I'm trying to run a query for SOX where we would like to show the grants of the accountadmin role and include the status of the users so we can see if the user is disabled or not. Is there a way to include the users status in the query:

show grants of role accountadmin

Upvotes: 1

Views: 380

Answers (2)

Michael Golos
Michael Golos

Reputation: 2069

You can always query SNOWFLAKE.ACCOUNT_USAGE views:

USE ROLE ACCOUNTADMIN;
SELECT g.ROLE, u.NAME, u.DISPLAY_NAME, u.DISABLED
  FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS AS g
  JOIN SNOWFLAKE.ACCOUNT_USAGE.USERS AS u ON g.GRANTEE_NAME = u.NAME
 WHERE g.ROLE = 'ACCOUNTADMIN';

Reference: ACCOUNT_USAGE Views, GRANTS_TO_ROLES View

Upvotes: 0

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1606

try this

show grants of role ACCOUNTADMIN;
SHOW USERS;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-1))) where "name" 
IN (Select "grantee_name" from table(result_scan(last_query_id(-2))) );

Upvotes: 1

Related Questions