I have a service broker queue that is serviced by a stored procedure which calls another stored procedure to run an SSIS package.
This second procedure runs the SSIS package by creating a Job and running it (see Calling a SSIS Package from a Stored Procedure
The problem I'm having is that when the queue is serviced I get an error saying that the user does not have execute permissions for the sp_add_job procedure.
The user running the process is in the SQLAgentOperator role in msdb
When running the second stored procedure directly (the one that creates the job) it works perfectly.
It's just when that procedure is run from the service broker procedure that it fails.
The code to set up the procedure to service the queue is
ALTER QUEUE ProcessUploadQueue
( STATUS = ON,
PROCEDURE_NAME = Upload,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'SPUser'
Does anyone have any ideas what I might be doing wrong.