How to Create a User in Snowflake: Step-by-Step Guide
To create a user in Snowflake, use the
CREATE USER command followed by the username and optional parameters like password and default role. This command sets up a new user account for accessing Snowflake securely.Syntax
The CREATE USER command in Snowflake is used to add a new user account. You specify the username and can include options like PASSWORD, DEFAULT_ROLE, and DEFAULT_WAREHOUSE to configure the user's access and environment.
- username: The name of the new user.
- PASSWORD: The user's password (optional if using external authentication).
- DEFAULT_ROLE: The role assigned by default to the user.
- DEFAULT_WAREHOUSE: The warehouse assigned by default for queries.
- COMMENT: Optional description for the user.
sql
CREATE USER username [ PASSWORD = 'password' ] [ DEFAULT_ROLE = role_name ] [ DEFAULT_WAREHOUSE = warehouse_name ] [ COMMENT = 'description' ];
Example
This example creates a user named john_doe with a password, default role, and default warehouse. It shows how to set up a user ready to access Snowflake.
sql
CREATE USER john_doe PASSWORD = 'StrongPassw0rd!' DEFAULT_ROLE = analyst DEFAULT_WAREHOUSE = compute_wh COMMENT = 'Analyst user account';
Output
User JOHN_DOE successfully created.
Common Pitfalls
Common mistakes when creating users include:
- Using weak or simple passwords that Snowflake rejects.
- Not assigning a default role, which can cause access issues.
- Forgetting to grant necessary privileges to the user after creation.
- Trying to create a user that already exists.
Always ensure the password meets Snowflake's complexity rules and assign roles and privileges properly.
sql
/* Wrong: Missing password and default role */ CREATE USER jane_doe; /* Right: Include password and default role */ CREATE USER jane_doe PASSWORD = 'S3curePass!' DEFAULT_ROLE = analyst;
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| username | Name of the new user | john_doe |
| PASSWORD | User's password (optional if external auth) | 'StrongPassw0rd!' |
| DEFAULT_ROLE | Role assigned by default | analyst |
| DEFAULT_WAREHOUSE | Warehouse assigned by default | compute_wh |
| COMMENT | Description of the user | 'Analyst user account' |
Key Takeaways
Use the CREATE USER command with username and optional parameters to add a new Snowflake user.
Always assign a DEFAULT_ROLE to ensure the user has proper access rights.
Set a strong password that meets Snowflake's security requirements unless using external authentication.
Remember to grant necessary privileges to the user after creation for effective access.
Avoid creating users with duplicate names to prevent errors.
