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
This all worked without problem for me:
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.