fivetran_testing_table Does Not Exist or Not Authorized Error

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. 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.