How to Set Default Role for User in Snowflake
To set a default role for a user in Snowflake, use the
ALTER USER command with the DEFAULT_ROLE parameter. This sets the role that the user automatically assumes upon login.Syntax
The syntax to set a default role for a user in Snowflake is:
ALTER USER <username> SET DEFAULT_ROLE = <role_name>;
This command changes the default role that the user assumes when they log in.
Parts explained:
ALTER USER <username>: Specifies which user to modify.SET DEFAULT_ROLE = <role_name>: Assigns the default role for that user.
sql
ALTER USER username SET DEFAULT_ROLE = role_name;
Example
This example sets the default role ANALYST_ROLE for the user john_doe. When john_doe logs in, Snowflake automatically assigns this role.
sql
ALTER USER john_doe SET DEFAULT_ROLE = ANALYST_ROLE;
Output
User JOHN_DOE altered.
Common Pitfalls
Common mistakes when setting default roles include:
- Using a role name that does not exist or is misspelled.
- Not having the necessary privileges to alter the user.
- Expecting the default role to grant all permissions without assigning proper grants.
Always verify the role exists and you have SECURITYADMIN or equivalent privileges.
sql
/* Wrong: role does not exist */ ALTER USER john_doe SET DEFAULT_ROLE = NON_EXISTENT_ROLE; /* Right: use existing role */ ALTER USER john_doe SET DEFAULT_ROLE = ANALYST_ROLE;
Output
SQL execution error: Role 'NON_EXISTENT_ROLE' does not exist.
User JOHN_DOE altered.
Quick Reference
Summary tips for setting default roles:
- Use
ALTER USER <username> SET DEFAULT_ROLE = <role_name>. - Ensure the role exists before assigning.
- Only users with
SECURITYADMINor higher can alter users. - The default role is assigned at login automatically.
Key Takeaways
Use ALTER USER with DEFAULT_ROLE to set a user's default role in Snowflake.
Ensure the role you assign exists and you have permission to alter the user.
The default role is automatically assumed by the user at login.
Incorrect role names cause errors and prevent changes.
Only users with SECURITYADMIN or equivalent privileges can set default roles.
