System-defined roles (ACCOUNTADMIN, SYSADMIN, etc.) in Snowflake - Commands & Configuration

Introduction

Snowflake uses system-defined roles to control who can do what in your account. These roles help keep your data safe by giving different people the right access without confusion.

When you want to give full control of your Snowflake account to a trusted admin.

When you need to let someone manage databases and warehouses but not the whole account.

When you want to allow users to create and manage their own objects without affecting others.

When you want to separate duties so no one person has too much power.

When you want to quickly assign standard permissions without creating custom roles.

Commands

This command lists all roles available in your Snowflake account, including system-defined roles like ACCOUNTADMIN and SYSADMIN.

Terminal

SHOW ROLES;

Expected Output

name          created_on                  owner

ACCOUNTADMIN  2023-01-01 12:00:00.000    SYSADMIN

SYSADMIN      2023-01-01 12:00:00.000    ACCOUNTADMIN

PUBLIC        2023-01-01 12:00:00.000    ACCOUNTADMIN

Switches your current role to ACCOUNTADMIN, which has full control over the Snowflake account.

Terminal

USE ROLE ACCOUNTADMIN;

Expected Output

Role changed to ACCOUNTADMIN

Gives the user named alice the SYSADMIN role, allowing her to manage databases and warehouses.

Terminal

GRANT ROLE SYSADMIN TO USER alice;

Expected Output

Statement executed successfully.

Shows all roles and privileges granted to the user alice, confirming the SYSADMIN role assignment.

Terminal

SHOW GRANTS TO USER alice;

Expected Output

grantee_name  granted_role  granted_on

alice         SYSADMIN      USER

Key Concept

If you remember nothing else from this pattern, remember: system-defined roles in Snowflake provide ready-made permission sets to safely manage your account and data.

Common Mistakes

Trying to assign system-defined roles without using ACCOUNTADMIN role.

Only ACCOUNTADMIN can grant or revoke system-defined roles, so the command will fail.

Switch to ACCOUNTADMIN role first using 'USE ROLE ACCOUNTADMIN;' before granting system roles.

Assuming PUBLIC role grants no access and ignoring it.

PUBLIC role is automatically assigned to all users and can grant basic access, so ignoring it can cause unexpected permissions.

Review PUBLIC role privileges and adjust if needed to avoid unwanted access.

Summary

Use SHOW ROLES to see all system-defined roles in Snowflake.

Switch to ACCOUNTADMIN role to manage system roles and permissions.

Grant system-defined roles like SYSADMIN to users to assign standard permissions.

Verify role assignments with SHOW GRANTS TO USER command.