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

Attention

This behavior change is in the 2023_06 bundle.

For the current status of the bundle, refer to Bundle History.

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;

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

DROP ROLE r2;

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;

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