I am trying to create a task in schema1 which is calling a procedure proc1 in schema1. The procedure has multiple insert table statements which inserts data into schema2.table2 and schema3.table3 from schema1.table1. On execution the procedure throws an error with schema2.table2 does not exist or not authorized.
On running the procedure without the task, it executes and successfully completes with the role used for creation of the task.
Can someone help on this please.
Snowflake runs tasks with the privileges of the task owner, and depending on what types of stored procedure you have setup, either owner's right, or caller right, the privileges used to run the SP can be different.
If it is a caller's right SP, it will use the task owner's role to execute the queries inside the SP. If it is an owner's right SP, it will use the SP owner's role to execute those queries.
So, please check which type your SP is and the owners of SP and tasks to make sure those roles have correct privilege to access schema2.table2.
