Snowflake cannot use schema

447 Views Asked by At

I created new user, and new role. I assigned to the user that role. These are the role privileges:

GRANT OWNERSHIP ON SCHEMA <DB name>.<schema name> TO ROLE <role name>;
GRANT USAGE ON DATABASE <DB name> TO ROLE <role name>;

But I still failing on

USE SCHEMA <schema name>;

002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.

Edit 1: With new schema it does work, it stops working if the schema was created by another user with another role and then I grant the ownership of that schema to the newly create role

Edit 2: Also when I ran show SCHEMAS; the schema I granted ownership to, is not displayed, only the schemas that the user have created

1

There are 1 best solutions below

4
Felipe Hoffa On

This all worked without problem for me:

use role accountadmin;
create role delete_stacko;
grant role delete_stacko to role sysadmin;

create schema temp.delete_schema;
grant ownership on schema temp.delete_schema to role delete_stacko;
grant usage on database temp to role delete_stacko;

use role delete_stacko;
use schema temp.delete_schema;

I created a new role, a new schema, and when switching to that role I had all permissions needed to use that schema. Make sure to check that the schema is in the database you are trying to work with - or use prefixes to be completely sure.