I'm running DB2 9.1.5 on the Z/os. I'm getting an unusual user privilege error running a stored procedure created as one user and run as another. Basically it goes like this:

* log in as USER111
* create procedure MAIN.PROC1 { procedure updates table MAIN.TABLE }
* log in as USER222
* call MAIN.PROC1()
SQL0551N "USER111" does not have the privilege to perform operation "UPDATE" on object "MAIN.TABLE". SQLSTATE=42501

This is correct. USER111 does not have update privileges, but USER222 does and that is who is logged in and running the procedure. Any idea on why I am getting this error?

I might be wrong with respect to Z/OS, but I think this is exactly the expected behaviour. The SP package runs with the authorization ID that was used to bind it. You could try rebinding the package as user222 before running the SP.

davebert99, I think you just need to grant USER222 Execute authorization on the Stored Procedure. I have also seen some places that USER222 might need Execute authorization on the Package but I have had mixed results (most of the time I do NOT grant to the Package and the Stored Procedure can be executed with no problem.

Thanks all. I'll research those suggestions.

USER222 must already have Execute or they couldn't run the procedure to get that error. They would get:
SQL0551N "USER222" does not have the privilege to perform operation "CALL" on object "MAIN.PROC1".

Dave