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

user_7742692 0
Viewed 49

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

snowflake-cloud-data-platform
2 Answers

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))) );
user_112279190

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

user_126071260
Related