Error: Insufficient Privileges To Operate on Schema FIVETRAN_TESTING_SCHEMA

Issue

While setting up and testing the destination, the following error appears:

SQL access control error: Insufficient privileges to operate on schema 'FIVETRAN_TESTING_SCHEMA_53D7B17A-009D-453B-9146-A2903B26C4BE'

Environment

Destination: Snowflake

Resolution

To resolve this issue, do the following:

1. Ensure the FIVETRAN_USER role has the appropriate permissions to operate on future schemas in the database. Run the following command, replacing <database_name> with the appropriate value:

   GRANT ALL ON future SCHEMAS IN DATABASE <database_name> TO role FIVETRAN_ROLE;
   

2. Verify that the FIVETRAN_USER role has ownership privileges for future tables in the database. Run the following command, replacing <database_name> with the appropriate value:

   GRANT ownership ON future TABLES IN DATABASE <database_name> TO role FIVETRAN_ROLE;
   

Cause

This issue occurs when the ownership of the staging schema we use is changed from its default configuration, preventing the FIVETRAN_USER role from managing future schemas or tables.