How to Manage Users in Snowflake

Galaxy Glossary

How do I create, alter, and drop users in Snowflake?

USER management commands let account administrators create, alter, drop, and secure Snowflake users.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why manage users in Snowflake?

User objects hold login credentials, default roles, warehouses, and session parameters. Proper management secures data, enforces least-privilege, and streamlines onboarding.

How do I create a user?

Use CREATE USER with password, default_role, warehouse, and optional RSA key. Specify must_change_password = TRUE for first-time logins.

Example

CREATE USER dev_jane PASSWORD = 'Temp_1234' DEFAULT_ROLE = analyst DEFAULT_WAREHOUSE = dev_wh MUST_CHANGE_PASSWORD = TRUE;

How can I alter a user’s settings?

ALTER USER adjusts passwords, session defaults, namespaces, and security keys without dropping the account.

Example

ALTER USER dev_jane SET DEFAULT_NAMESPACE = analytics, DISABLED = FALSE;

How do I safely drop a user?

Revoke all roles, then DROP USER to avoid orphaned access. Always audit with SHOW GRANTS.

Example

REVOKE ROLE analyst FROM USER dev_jane; DROP USER dev_jane;

How do I grant data access to a new user?

Create a role with scoped privileges on ecommerce tables, grant the role to the user, and set it as the default.

Example

CREATE ROLE orders_reader; GRANT SELECT ON TABLE analytics.orders TO ROLE orders_reader; GRANT ROLE orders_reader TO USER dev_jane; ALTER USER dev_jane SET DEFAULT_ROLE = orders_reader;

Best practices for Snowflake user management

Automate onboarding with scripts, enforce strong passwords/keys, rotate credentials, and pair each user with least-privilege roles.

What are common mistakes?

Granting accountadmin role to regular users and forgetting to revoke roles before dropping a user are frequent errors. Fix by using scoped roles and ejecting privileges first.

Why How to Manage Users in Snowflake is important

How to Manage Users in Snowflake Example Usage


-- Onboard analyst for ecommerce data
CREATE USER analyst_amy PASSWORD = 'TempPass!1' DEFAULT_ROLE = orders_reader DEFAULT_WAREHOUSE = prod_wh MUST_CHANGE_PASSWORD = TRUE;

CREATE ROLE orders_reader;
GRANT SELECT ON TABLE analytics.orders TO ROLE orders_reader;
GRANT ROLE orders_reader TO USER analyst_amy;

How to Manage Users in Snowflake Syntax


-- Create a user
CREATE USER <username>
    PASSWORD = '<string>'
    [LOGIN_NAME = '<string>']
    [DEFAULT_ROLE = <role_name>]
    [DEFAULT_WAREHOUSE = <warehouse_name>]
    [DEFAULT_NAMESPACE = <db.schema>]
    [MUST_CHANGE_PASSWORD = TRUE|FALSE]
    [RSA_PUBLIC_KEY = '<pem_key>']
    [COMMENT = '<text>'];

-- Alter a user
ALTER USER <username>
    SET { PASSWORD = '<new_pw>' | RSA_PUBLIC_KEY = '<pem_key>' | DEFAULT_ROLE = <role> | DEFAULT_WAREHOUSE = <wh> | DEFAULT_NAMESPACE = <db.schema> | DISABLED = TRUE|FALSE };

-- Drop a user
DROP USER <username>;

-- Show grants to audit access
SHOW GRANTS ON USER <username>;

-- Ecommerce example: read-only access to Orders table
CREATE ROLE orders_reader;
GRANT SELECT ON TABLE analytics.orders TO ROLE orders_reader;
GRANT ROLE orders_reader TO USER <username>;

Common Mistakes

Assigning the powerful ACCOUNTADMIN role to standard users. This overrides least-privilege and risks accidental data loss. Fix: create scoped roles and grant only necessary privileges.
Dropping a user without revoking roles, leaving orphaned privileges. Fix: run SHOW GRANTS, REVOKE roles, then DROP USER.

Frequently Asked Questions (FAQs)

Can I manage users with SSO only?

Yes. Provision users in your IdP, then map them to SCIM-created Snowflake users and roles for seamless login.

How do I force a password change?

Set MUST_CHANGE_PASSWORD = TRUE in CREATE/ALTER USER. The user must update credentials at next login.

What happens to objects owned by a dropped user?

Ownership shifts to the role that owned the objects, not the user. Always verify role ownership before deletion.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie