Troubleshoot dynamic table permission issues¶

This page helps you diagnose and resolve permission-related dynamic table failures. For refresh failures unrelated to permissions, see Troubleshoot dynamic table refresh issues. For creation-time issues, see Troubleshoot dynamic table creation issues.

Refresh fails because secondary roles are not used¶

A query that succeeds interactively can fail during refresh because refreshes use only the owner role: secondary roles are not activated.

Typical error messages:

SQL access control error: Insufficient privileges to operate on table 'MY_DB.OTHER_SCHEMA.LOOKUP_TABLE'.

SQL compilation error: Object 'MY_DB.OTHER_SCHEMA.LOOKUP_TABLE' does not exist or not authorized.

Identify which objects the refresh can’t access. Check the refresh history for the error details:

SELECT name, state, state_message, query_id
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders',
    ERROR_ONLY => TRUE
))
ORDER BY refresh_start_time DESC
LIMIT 1;

Grant the missing privileges directly to the dynamic table’s owner role:

-- Find the owner role
SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA my_db.my_schema;
-- Check the "owner" column in the output

-- Grant the missing privilege to the owner role
GRANT SELECT ON TABLE my_db.other_schema.lookup_table TO ROLE transform_role;
GRANT USAGE ON SCHEMA my_db.other_schema TO ROLE transform_role;

If the privileges come from a database role, grant that database role to the dynamic table’s owner role.

Important

Do not rely on secondary roles for dynamic table access. Every object referenced in the definition must be accessible through the owner role’s primary grants.

Refresh fails after ownership transfer¶

When you transfer ownership of a dynamic table to a different role (using GRANT OWNERSHIP), the new owner role might not have all the privileges that the original owner had. Refreshes start failing because the new role can’t access the warehouse, base tables, or functions.

SQL access control error: Insufficient privileges to operate on warehouse 'TRANSFORM_WH'.

Check which role now owns the dynamic table:

SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA my_db.my_schema;

+------------+-------+------------------+
| name       | owner | ...              |
+------------+-------+------------------+
| DT_ORDERS | NEW_ROLE | ...           |
+------------+-------+------------------+

Grant the new owner role all privileges needed to refresh the dynamic table:

-- Warehouse access
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE new_role;

-- Source table access
GRANT SELECT ON TABLE my_db.my_schema.raw_orders TO ROLE new_role;

-- If the definition uses UDFs
GRANT USAGE ON FUNCTION my_db.my_schema.my_udf(VARCHAR) TO ROLE new_role;

-- Database and schema access
GRANT USAGE ON DATABASE my_db TO ROLE new_role;
GRANT USAGE ON SCHEMA my_db.my_schema TO ROLE new_role;

After granting the privileges, resume the dynamic table if it was auto-suspended due to consecutive failures:
```
ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;
```

Tip

Before transferring ownership, run SHOW GRANTS TO ROLE <current_owner> to capture the full list of grants. Replicate those grants to the new owner role before the transfer.

Row access policies or masking policies block incremental refresh¶

Row access and masking policies on base tables can block incremental refresh if they call context functions like CURRENT_ROLE or IS_ROLE_IN_SESSION. These functions require a full session context that incremental refresh does not provide.

Typical error messages:

Query contains context functions on which change tracking is not supported.

Incremental refresh is not supported for queries referencing tables with row access policies
that use context functions.

Identify which base tables have row access or masking policies:

-- Check for row access policies
SELECT * FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME => 'my_db.my_schema.raw_orders',
    REF_ENTITY_DOMAIN => 'TABLE'
));

Resolution options:

Option A: Switch to FULL refresh mode. Full refresh evaluates the entire query from scratch on each refresh, so context functions in policies work correctly:
```
CREATE OR REPLACE DYNAMIC TABLE my_db.my_schema.dt_orders
    TARGET_LAG = '30 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = FULL
AS
    SELECT order_id, customer_id, order_date
    FROM my_db.my_schema.raw_orders;
```
Option B: Apply the policy on the dynamic table instead. Remove the policy from the base table and apply it to the dynamic table. Since the policy runs at query time (not refresh time), context functions work correctly:
```
ALTER TABLE my_db.my_schema.raw_orders
    DROP ROW ACCESS POLICY my_policy;

ALTER DYNAMIC TABLE my_db.my_schema.dt_orders
    ADD ROW ACCESS POLICY my_policy ON (customer_id);
```

IS_ROLE_IN_SESSION() only accepts constant arguments in incremental mode¶

When a dynamic table uses incremental refresh and the definition references a base table with a row access policy that calls IS_ROLE_IN_SESSION(), the policy function must use only constant string arguments. Variable or column-based arguments are not supported in incremental mode.

IS_ROLE_IN_SESSION() with non-constant arguments is not supported for incremental refresh.

Check the row access policy definition to see how IS_ROLE_IN_SESSION() is called:
```
SELECT GET_DDL('POLICY', 'my_db.my_schema.my_policy');
```
If the policy uses a column reference as the argument (for example, IS_ROLE_IN_SESSION(allowed_role)), this is not supported in incremental mode.
Resolution options:
- Rewrite the policy to use constant role names: IS_ROLE_IN_SESSION('ANALYST_ROLE').
- Move the policy from the base table to the dynamic table, where it runs at query time rather than refresh time.
- Switch the dynamic table to REFRESH_MODE = FULL.

Projection policies cause unexpected refresh behavior¶

Projection policies on base table columns can affect dynamic table refresh behavior. If a projection policy restricts a column the definition references, the refresh either sees masked data (producing incorrect output) or fails outright if the owner role is not in the policy’s allowed list.

Check if any base tables have projection policies:

SELECT * FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME => 'my_db.my_schema.raw_orders',
    REF_ENTITY_DOMAIN => 'TABLE'
))
WHERE policy_kind = 'PROJECTION_POLICY';

If the dynamic table’s owner role is not in the projection policy’s allowed list, the refresh sees masked or restricted data. Grant the owner role the appropriate access or modify the projection policy:
```
-- Option: Ensure the owner role is authorized in the projection policy
-- Check your projection policy definition and update it to include the owner role
```
If you want the dynamic table to contain the unmasked data (for downstream consumption with separate access controls), ensure the owner role has full access through the projection policy.

Can’t see dynamic table metadata¶

If SHOW DYNAMIC TABLES or INFORMATION_SCHEMA.DYNAMIC_TABLES() returns no rows for a dynamic table you know exists, the issue is a missing privilege.

Verify that the dynamic table exists and your role can see it:
```
SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA my_db.my_schema;
```
If this returns no rows, your role may also need USAGE on the database and schema.

Check whether your role has the MONITOR privilege on the dynamic table:

SHOW GRANTS ON DYNAMIC TABLE my_db.my_schema.dt_orders;

+-------------------------------+-----------+---------------+------------+------------+--------------+
| created_on                    | privilege | granted_on    | name       | granted_to | grantee_name |
+-------------------------------+-----------+---------------+------------+------------+--------------+
| 2025-01-15 08:00:00.000 -0800 | MONITOR   | DYNAMIC_TABLE | DT_ORDERS | ROLE       | ANALYST_ROLE |
+-------------------------------+-----------+---------------+------------+------------+--------------+

If your role doesn’t have MONITOR, ask the dynamic table owner to grant it:
```
GRANT MONITOR ON DYNAMIC TABLE my_db.my_schema.dt_orders TO ROLE analyst_role;
```

For the full list of privileges required for dynamic table operations, see Dynamic table access control.

Refresh fails with EXECUTE AS USER permission errors¶

When a dynamic table uses EXECUTE AS USER, refreshes can fail if the required privileges or user configuration are missing.

Missing IMPERSONATE privilege. The dynamic table’s owner role must hold IMPERSONATE on the target user. If this privilege is missing or was revoked, refreshes fail with a permission error. Grant IMPERSONATE on the target user to the dynamic table’s owner role:

GRANT IMPERSONATE ON USER service_user TO ROLE transform_role;

User does not exist. The user specified in EXECUTE AS USER must exist. If the user was dropped or never created, refreshes fail. Recreate the user and ensure it holds the dynamic table’s owner role:

CREATE USER IF NOT EXISTS service_user;
GRANT ROLE transform_role TO USER service_user;

After resolving the issue, resume the dynamic table if it was auto-suspended:

ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;

What’s next¶

To troubleshoot refresh failures on an existing dynamic table, see Troubleshoot dynamic table refresh issues.
To troubleshoot creation-time issues, see Troubleshoot dynamic table creation issues.
For the complete privilege reference, see Dynamic table access control.
To set up monitoring and alerts, see Monitor dynamic tables.