Snowflake 'Object does not exist or not authorized': fixing role and grant issues

#The error hides the reason

SQL compilation error: Object 'ANALYTICS.SALES.ORDERS' does not exist or not authorized.

Snowflake gives the same message whether the object doesn't exist or the role can't see it. Check access before you check spelling.

#1. Which role is active?

SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_SECONDARY_ROLES(), CURRENT_WAREHOUSE();
SHOW GRANTS TO USER jane;

Tools (BI, dbt, JDBC) often connect with a default role that isn't the one you tested with. Either set the role in the connection, or allow secondary roles:

USE SECONDARY ROLES ALL;               -- privileges from all granted roles (for queries)
ALTER USER jane SET DEFAULT_ROLE = analyst;

#2. The USAGE chain

To SELECT from a table, the role needs:

GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst;
GRANT USAGE ON DATABASE analytics TO ROLE analyst;
GRANT USAGE ON SCHEMA analytics.sales TO ROLE analyst;
GRANT SELECT ON TABLE analytics.sales.orders TO ROLE analyst;

Missing USAGE on the database or schema is the usual cause.

#3. New tables aren't visible: future grants

Grants on existing tables don't cover tables created later (for example, by tonight's dbt run).

GRANT SELECT ON ALL TABLES IN SCHEMA analytics.sales TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.sales TO ROLE analyst;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA analytics.sales TO ROLE analyst;

Note

If future grants exist at both the database and schema level, the schema-level ones take precedence for that schema. A schema-level future grant to another role can silently "steal" new tables from your database-level grant.

#4. Ownership and managed access

SHOW GRANTS ON TABLE analytics.sales.orders;   -- look for OWNERSHIP
SHOW SCHEMAS LIKE 'SALES' IN DATABASE analytics; -- 'options' shows MANAGED ACCESS

• In a managed access schema, only the schema owner (or a role with MANAGE GRANTS) can grant on its objects, not the object owner.
• When a table is recreated (CREATE OR REPLACE), existing grants are lost unless you use COPY GRANTS or future grants cover it.

#5. Role hierarchy

Grant functional roles to higher roles so administrators inherit access:

GRANT ROLE analyst TO ROLE sysadmin;
SHOW GRANTS OF ROLE analyst;   -- who has this role

#Quick test as the user's role

USE ROLE analyst;
USE WAREHOUSE bi_wh;
SELECT COUNT(*) FROM analytics.sales.orders;

Tip

Put grants in code (Terraform's Snowflake provider, or a dbt post-hook / grants config) so they're applied the same way every time and survive CREATE OR REPLACE.