GRANT OWNERSHIP command: Ownership transfer not allowed for shared databases¶

The GRANT OWNERSHIP and DROP ROLE commands behave as follows:

Previously:

When you grant the USAGE privilege on a database to a share, you can execute the GRANT OWNERSHIP command to transfer the OWNERSHIP privilege on the database to a different role. For example:

GRANT USAGE ON DATABASE mydb TO SHARE myshare;
GRANT OWNERSHIP ON DATABASE mydb TO ROLE r2 REVOKE CURRENT GRANTS;

Copy

Additionally, you can drop the role that has the OWNERSHIP privilege on the shared database:

DROP ROLE r2;

Copy

Currently:

You can transfer ownership of the shared database to a different role and use the COPY CURRENT GRANTS clause, however you cannot transfer ownership on the shared database to a different role and use the REVOKE CURRENT GRANTS clause. If you try to do this, Snowflake returns the following error message:

Cannot transfer ownership on a database that is granted to a share

To avoid this error message and transfer the OWNERSHIP privilege to a different role, revoke the USAGE privilege on the database from the share, transfer the OWNERSHIP privilege on the database to a different role, and grant the USAGE privilege on the database to the share. For example:

REVOKE USAGE ON DATABASE mydb FROM SHARE myshare;
GRANT OWNERSHIP ON DATABASE mydb TO ROLE r2;
GRANT USAGE ON DATABASE mydb TO SHARE r2;

Copy

Additionally, if you try to drop the role that has the OWNERSHIP privilege on the shared database, Snowflake returns the following error message with instructions on the actions to take:

Cannot drop a role that is the owner of one or more shared databases. Run 'SHOW GRANTS TO ROLE <role_name>' to find these shared
databases and transfer their ownership to appropriate role using 'GRANT OWNERSHIP ON DATABASE <database_name> TO ROLE
<target_role_name> COPY CURRENT GRANTS'.

Ref: 1181