We are getting the error 9936 "The user does not have access to zone STG_DB" while calling DBC.SYSEXECSQL. Following is the code of stored procedure:
DECLARE vSQL_Text VARCHAR(5000);
SET vSQL_Text = 'INSERT INTO STG_DB.STG_786_CUSTOMER SELECT * FROM STG_DB.STG_786_CUSTOMER;';
However, if we don't use DBC.SYSEXECSQL and run the query directly (code given below) it works fine without any error.
INSERT INTO STG_DB.STG_786_CUSTOMER SELECT * FROM STG_DB.STG_786_CUSTOMER;
Can someone help that why this error is coming up. We are using Teradata zones on version 15.10.00.01
The Invoker. Even though that I have granted all rights to invoker on both databases (i.e. the database where procedure reside and the target database )
GRANT ALL ON STG_DB TO ETL_User;
GRANT ALL ON SP_DB TO ETL_User;
I still receive the same error message
error 9936 "The user does not have access to zone of STG_DB"
Thanks for your response. Following are the details:
Creator user of the SP: ROOT_CANADA
Caller user of the SP: ETL_USR
Database that contains SP: GDEV1P
Please note that we are only facing this issue when we use zones in TD 15.10.00.01. Otherwise it works fine on the older TD versions.