I can't execute an SP that calls another SP inside

Database
Enthusiast

I can't execute an SP that calls another SP inside

Hello,

I'm trying to execute a procedure (F_SEPARA_LETRAS_Y_NUMEROS) with another procedure called inside (F_QUITA_REPES) but I get the following error:
  • CALL Failed. [3523] F_NOTAS_AL_FINAL: An owner referenced by user does not have EXECUTE PROCEDURE access to novaquality.F_QUITA_REPES
Both procedures were created with "SQL SECURITY CREATOR" and by the same user, the same one I'm trying to execute it with.

Also, if I execute the procedure inside (F_QUITA_REPES) on its own, it works.

What I do I have to do to be able to execute the one with the SP called inside?

Thanks and regards

Juan
Tags (1)
3 REPLIES
Teradata Employee

Re: I can't execute an SP that calls another SP inside

GRANT EXECUTE PROCEDURE ON novaquality.F_QUITA_REPES TO database_containing_calling_procedure;

(Or GRANT on the entire database instead of the individual SP.)

Enthusiast

Re: I can't execute an SP that calls another SP inside

Thanks a lot! It has worked!
  • GRANT EXECUTE PROCEDURE ON novaquality TO novaquality;
I never thought I would have to give permissions to the database to execute its own procedures...

So if you just execute a procedure with no other procedures inside (or just functions), you just have to give permisssions to the user on the database, but if the procedure calls another one inside, you also have to give permissions to the database on itself. Is that the conclusion of this?

Best regards!

Juan
Teradata Employee

Re: I can't execute an SP that calls another SP inside

For CREATE / REPLACE PROCEDURE, the CREATOR must have permission to execute any DML statements within the procedure (including CALL).

But the OWNER (database in which the procedure is defined) must have those permissions for execution.

Other restrictions may apply depending on the SQL SECURITY option; details are in the SQL Reference Manual (DDL Syntax volume) under the topic for CREATE PROCEDURE.