We can see what roles are assigned to a user but how do I see the list of users in a role snowflake?
For example I have a role svn_dev_admin , I need to see all users under this role Thanks, Xi
The following query should give you users list for the role specified and the role(s) under that.
-- since role_name used in the query twice, set it to a parameter
set role_name = 'svn_dev_admin';
select GRANTEE_NAME
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
where ROLE = $role_name
and DELETED_ON is null
union
select GRANTEE_NAME
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
where ROLE IN (select GRANTEE_NAME
from SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
where privilege = 'USAGE'
and GRANTED_ON = 'ROLE'
and NAME = $role_name)
and DELETED_ON is null;
If you want to see the users got the access with a lower level role to the given role you can add the ROLE
column to the query like the following, but it might create duplicate user names
select ROLE, GRANTEE_NAME