How to Assign Role to User in Snowflake: Simple Steps
To assign a role to a user in Snowflake, use the
GRANT ROLE <role_name> TO USER <user_name> command. This command gives the specified user the permissions associated with that role.Syntax
The command to assign a role to a user in Snowflake follows this pattern:
GRANT ROLE <role_name> TO USER <user_name>: Grants the specified role to the user.<role_name>: The name of the role you want to assign.<user_name>: The name of the user who will receive the role.
This command must be run by a user with the OWNERSHIP privilege on the role or the SECURITYADMIN role.
sql
GRANT ROLE role_name TO USER user_name;
Example
This example shows how to assign the role ANALYST to the user john_doe. After running this command, john_doe will have all permissions granted to the ANALYST role.
sql
GRANT ROLE ANALYST TO USER john_doe;
Output
Statement executed successfully.
Common Pitfalls
Common mistakes when assigning roles include:
- Trying to grant a role without sufficient privileges. You must have
OWNERSHIPon the role or beSECURITYADMIN. - Using incorrect role or user names. Names are case-sensitive unless quoted.
- Forgetting to activate the role after assignment. The user must switch to the new role to use its permissions.
sql
/* Wrong: User lacks privileges to grant role */ GRANT ROLE ANALYST TO USER john_doe; /* Right: Logged in as SECURITYADMIN or role owner */ USE ROLE SECURITYADMIN; GRANT ROLE ANALYST TO USER john_doe;
Quick Reference
| Command | Description |
|---|---|
| GRANT ROLE | Assigns a role to a user. |
| REVOKE ROLE | Removes a role from a user. |
| SHOW GRANTS TO USER | Lists roles and privileges granted to a user. |
| USE ROLE | Activates a role for the current session. |
Key Takeaways
Use the GRANT ROLE command to assign roles to users in Snowflake.
You need proper privileges like SECURITYADMIN or role ownership to grant roles.
Role and user names are case-sensitive unless quoted.
Users must activate the assigned role to use its permissions.
Check assigned roles with SHOW GRANTS TO USER command.
