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

A stored procedure returns a single row that contains a single column; it is not designed to return a result set. However, if your result set is small enough to fit into a single value of type VARIANT or ARRAY, you can return a result set from a stored procedure with some additional code- here

Hope this helps!

It works for delete statement! Example below:

use role sysadmin;
create or replace database DATA_IMPORTS_SO;
create or replace table ProductMaster_SO (id integer, name varchar);
insert into ProductMaster_SO values (1,'stackoverflow');
insert into ProductMaster_SO values (2,'stackoverflow');
select count(*) FROM DATA_IMPORTS_SO.PUBLIC.ProductMaster_SO;--2 rows
create or replace procedure Clenup_DI_Products()
returns string not null
language javascript
EXECUTE AS OWNER
as
$$
var sql_command = 
     "delete FROM DATA_IMPORTS_SO.PUBLIC.ProductMaster_SO";
    try {
        snowflake.execute (
            {sqlText: sql_command}
            );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
$$;

call Clenup_DI_Products();
select count(*) FROM DATA_IMPORTS_SO.PUBLIC.ProductMaster_SO;--0 rows

Answer 3

Ultimately I was unable to find a reason that the stored procedure wouldn't work. I ended up dropping the table and recreating it with an all caps name. After that I was able to run the stored procedure with no issues.

I'm unclear if the fix was creating the table with a UCASE name or recreating it with a USER ROLE SYSADMIN command.

Answer 4

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

Answer 5

Two things to check.

1. Enclose the table name in double-quotes. Check case.
2. GRANT SELECT ON ALL TABLES IN SCHEMA "SCHEMA_NAME" TO ROLE PUBLIC;