Issue

During Snowflake destination setup tests, the following error appears:

Permission Test: SQL compilation error: Object 'Database_Name."FIVETRAN_TESTING_SCHEMA_A8E15F33-76E1-4785-AAD2-CC785B6FF3E5".FIVETRAN_TESTING_TABLE' does not exist or not authorized.

Environment

Destination: Snowflake

Resolution

The future table owner grant is set to a user other than the Fivetran destination user. The default is FIVETRAN_USER. In this example, we run the query SHOW FUTURE GRANTS IN DATABASE FIVETRAN_DATABASE;

The grantee_name is set to AMT when it should be set to FIVETRAN_USER / FIVETRAN_ROLE. Please follow the steps below:

1. Find the user who owns the future table ownership with SHOW FUTURE GRANTS IN DATABASE FIVETRAN_DATABASE;
2. Revoke their access with REVOKE ownership ON FUTURE TABLES IN DATABASE <db_name> FROM <wrong_user>;
3. Grant the FIVETRAN_USER role the owner of future tables with

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

4. Grant the FIVETRAN_USER role the owner of future schemas with GRANT ALL ON future SCHEMAS IN DATABASE <db_name> TO role FIVETRAN_ROLE;

Cause

The future ownership of the staging tables Fivetran uses was changed from the default.