Snowflake: Execution error in store procedure : SQL compilation error: Object does not exist or not authorized. At Statement.execute

Question

I'm totally confused by the permission model of the Snowflake system. I created a database, created a stored procedure within that database, and tried to call that stored procedure all with the same user in the SYSADMIN role. I get the error "Execution error in store procedure: SQL compilation error: Object does not exist or not authorized. At Statement.execute"

I'm not even sure where to start. How does my user not have permission to a table that was created by said user?

Answer 1

Check the casing of the name of the objects you are referring to. If you for example created the table wrapped in double quotes, it's case sensitive. Snowflake automatically converts unquoted identifiers to UPPER case.

Example:

    CREATE TABLE test1 (
    test nvarchar)

    CREATE TABLE "teSt2" (
    test nvarchar)

   -- This works
    select * from test1

   -- This doesn't work because the table was created wrapped in double quotes and with a capital S in the name
    select * from test2

   -- This doesn't work either because it will convert to UPPER
    select * from teSt2

   -- This works
    select * from "teSt2"

Answer 2

It may not be a problem with the case sensitivity of the table but it might be related to the rights of the stored procedure (Function) you created.

Changing your code as below might do the trick.

EXECUTE AS OWNER -- Present
EXECUTE AS CALLER -- Change

To understand more on owner's rights and caller's right of the Stored Procedure, please follow below link. https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html