SnowPro Associate: Platform Question 142
Single answer2.2 Create a database, explore data, configure parameters, and transfer ownership.A Snowflake administrator creates a database named SALES_RAW and grants the ETL_ADMIN role full control of the database so that the data engineering team can manage it independently. To align with internal governance, the administrator must transfer ownership of the database to ETL_ADMIN while ensuring the transfer succeeds even though existing privileges have already been granted on the database to other roles. Which command should the administrator use?
- A
GRANT OWNERSHIP ON DATABASE SALES_RAW TO ROLE ETL_ADMIN
- B
ALTER DATABASE SALES_RAW SET OWNER = ETL_ADMIN
- C
GRANT OWNERSHIP ON DATABASE SALES_RAW TO ROLE ETL_ADMIN COPY CURRENT GRANTS
- D
TRANSFER OWNERSHIP OF DATABASE SALES_RAW TO ROLE ETL_ADMIN REVOKE CURRENT GRANTS
Show answer and explanation
Correct answer: C
Explanation
In Snowflake, ownership is a special privilege and can be transferred only with the GRANT OWNERSHIP command. When transferring ownership of an object such as a database, Snowflake requires the administrator to specify how existing outbound privileges should be handled if they are present. The COPY CURRENT GRANTS clause preserves those grants, while REVOKE CURRENT GRANTS removes them. In this scenario, preserving access for other roles is the safer and more practical choice, so GRANT OWNERSHIP ON DATABASE ... TO ROLE ... COPY CURRENT GRANTS is correct. This aligns with Snowflake access control behavior and documented best practices for object ownership transfers and privilege continuity.
- A. Incorrect.
Incorrect. In Snowflake, GRANT OWNERSHIP is the correct command family for transferring ownership, but when outbound privileges already exist on the object, the transfer must explicitly address them. Without specifying COPY CURRENT GRANTS or REVOKE CURRENT GRANTS, the ownership transfer can fail because Snowflake requires the administrator to define how existing grants should be handled.
- B. Incorrect.
Incorrect. Snowflake does not use an ALTER DATABASE ... SET OWNER syntax to transfer ownership. Ownership is transferred through the GRANT OWNERSHIP command, not by changing an OWNER property with ALTER DATABASE.
- C. Correct.
Correct. GRANT OWNERSHIP ON DATABASE SALES_RAW TO ROLE ETL_ADMIN COPY CURRENT GRANTS is the valid way to transfer ownership while preserving existing outbound privileges already granted on the database. This is the practical choice when other roles currently depend on those privileges and the administrator wants the transfer to succeed without disrupting access.
- D. Incorrect.
Incorrect. TRANSFER OWNERSHIP is not valid Snowflake SQL syntax. Also, while REVOKE CURRENT GRANTS is a valid clause used with GRANT OWNERSHIP, it would remove existing outbound grants rather than preserve them. The scenario explicitly requires the transfer to succeed even when privileges exist and implies a desire to avoid unnecessary disruption.