Search code examples
sqlstored-proceduressnowflake-cloud-data-platformcursor

Snowflake - how can I update user roles dynamically with a cursor?

I'm trying to to perform the code below.

My goal is to loop through users and update their role only if it's empty. Is this possible using a cursor this way (I want to call this from a stored procedure at the end of the day)?

Thank you!

DECLARE
    c1 CURSOR FOR select * from SNOWFLAKE.ACCOUNT_USAGE.USERS where default_role='';
    user_name string;
BEGIN
    OPEN c1;
    FOR user_iterator IN c1 DO
        user_name := user_iterator.LOGIN_NAME;
        alter user user_name set default_role = 'something';
        
    END FOR;
    CLOSE c1;
    RETURN 'success';
END;

I keep getting this error:

User 'USER_NAME' does not exist or not authorized.

Solution

  • probably you need to wrap it with identifier: alter user identifier(:user_name) set default_role = 'something';