Snowflake User's Default Role Does Not Exist

Question

I'm looking to create a database with access for a single user using the following script:

USE ROLE ACCOUNTADMIN;
CREATE DATABASE db;
USE DATABASE db;
CREATE WAREHOUSE wh
    WAREHOUSE_SIZE = SMALL
    ;
CREATE ROLE new_role;
GRANT USAGE ON DATABASE db TO ROLE new_role;
GRANT CREATE SCHEMA ON DATABASE db TO ROLE new_role;
GRANT ALL ON SCHEMA db.PUBLIC TO ROLE new_role;
CREATE or replace USER new_user PASSWORD='' DEFAULT_ROLE = 'new_role' DEFAULT_WAREHOUSE='wh';
GRANT ROLE new_role TO USER new_user;
GRANT ALL ON WAREHOUSE wh TO new_role;

When attempting to login with the newly created user, or connect from another service, I see the following error:

User's configured default role 'new_role' does not exist or not authorized. Contact your local system administrator, or attempt to login using a CLI client with a connect string selecting another

Setting the default role to PUBLIC allows me to log in from the Snowflake web portal. I can then select the desired role and navigate as expected. The third party connection cannot access any of the newly created objects, presumably because it does not attempt to switch roles.

What is missing to allow the default role to be set for my new user?

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-11-14T06:59:05

You should remove the quotes around the role name: DEFAULT_ROLE = new_role

ROLE names are case sensitive, and the syntax is just like column names:
COL_NAME, col_name and "COL_NAME" are the same, but "col_name" is different, ie. lower case.

I would recommend you not to quote such identifiers, and let the system implicitly transform references in whatever casing to uppercase. This is what most people do.

All these ways of setting the DEFAULT_ROLE are valid and equal:

ALTER USER u SET DEFAULT_ROLE = NEW_ROLE;
ALTER USER u SET DEFAULT_ROLE = new_role;
ALTER USER u SET DEFAULT_ROLE = "NEW_ROLE";
ALTER USER u SET DEFAULT_ROLE = 'NEW_ROLE';

The following however are different from above, as ROLE names are case sensitive:

ALTER USER u SET DEFAULT_ROLE = 'new_role';
ALTER USER u SET DEFAULT_ROLE = "new_role";

The DEFAULT_ROLE is just a text property and can be set to anything, eg. non-existing roles or roles that are not granted to the user. Both will generate errors at login.

Use the following command to see the exact name of the DEFAULT_ROLE setting in effect:

DESC USER u;

NB: The DEFAULT_ROLE setting is not a role grant !
You need to use GRANT ROLE r TO USER u also.

Snowflake User's Default Role Does Not Exist

2 Answers