Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 SECURITYADMIN or 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.