snowflake-cloud-data-platformsnowflake-schema

Table Visibility Issue in Specific Snowflake Schema via ODBC Connection

I am using a third-party application that connects to Snowflake using the ODBC driver. I can connect and view the database tables from several schemas; however, for one particular schema, I can connect successfully but the tables in that schema do not appear. Any suggestions on what I could check?

Solution

  • I suggest to check current role used for connecting via ODBC:

    SELECT CURRENT_ROLE(), CURRENT_SECONDARY_ROLES();

    Second, privileges for that role:

    SHOW GRANTS TO ROLE <role_name>;

    for one particular schema, I can connect successfully but the tables in that schema do not appear.

    If schema is visible it means that USAGE is granted on database and schema level, but there is no SELECT right assigned:

    GRANT SELECT ON TABLE mydb.myschema.mytable TO ROLE <role_name>;
-- GRANT SELECT ON ALL TABLES IN SCHEMA mydb.myschema TO ROLE <role_name>;

    Alternatively switch to a role that has those privileges already granted.