Power bi snowflake Default_role setting

Question

Is there a way to set default roles to all user that belong to specific role ?

I can manually alter the role to default for one user at a time, but it is time consuming and people come and go.

Answer 1

To answer your question directly: Setting the default role for multiple users within one statement isn't possible. It isn't possible to mention multiple users within one single ALTER USER-statement: https://docs.snowflake.com/en/sql-reference/sql/alter-user.html

Alternative solutions are:

1. Use Mike Walton's idea in the comments of your question and create a procedure that selects all users of a certain role and generates/executes an ALTER-statement for every user within the procedure (I like that one)
2. I once had a similar problem and created an Excel-sheet for all my users and roles. Here I maintained which user belonged to which role and used Excel to generate all my corresponding CRAETE ROLE, CREATE USER, GRANT ROLE and also ALTER USER-statements. So Excel generates the code based on your USER-ROLE-matrix and then you can paste the code to Snowflake. (Of course then you can extend and adapt that "excel code generator" for your use cases etc.)

Kind regards :-)