I am trying to change the OWNERSHIP from one role to other role in Snowflake DWH. Say Old Owner Role is the role with current Ownership, New Owner Roleis the role to which I wish to give ownership and R3 be a role which already has some access to the object of interest. I am getting below error when I am trying to do so.
SQL execution error: Dependent grant of privilege 'SELECT' on securable '' to role 'R3' exists. It must be revoked first. More than one dependent grant may exist
Why should an existing role be a problem for Ownership change?
Kannan, according to the Snowflake documentation (https://docs.snowflake.net/manuals/sql-reference/sql/grant-ownership.html), ownership of an object can't be granted (transferred) to another role unless all current privileges granted on the object have been revoked (using the REVOKE command). It looks like either the current owner or a member of a role with the appropriate privileges previously granted the SELECT privilege on the object (presumably a table or view) to the R3 role. That privilege will need to be revoked before ownership of the object can be transferred.
According to the same doc page, the restriction exists to prevent the new owner from unknowingly inheriting the object with grants already made on it.