All Forums Database
juanalfonso 41 posts Joined 01/16
01 Sep 2016
I can't execute an SP that calls another SP inside


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

Fred 1096 posts Joined 08/04
01 Sep 2016

GRANT EXECUTE PROCEDURE ON novaquality.F_QUITA_REPES TO database_containing_calling_procedure;
(Or GRANT on the entire database instead of the individual SP.)

juanalfonso 41 posts Joined 01/16
02 Sep 2016

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!

Fred 1096 posts Joined 08/04
02 Sep 2016

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.

You must sign in to leave a comment.