Service Broker Stored Procedure Can't Create Job

Question

Service Broker Stored Procedure Can't Create Job

Stuart Pearson

SSChasing Mays

Points: 637
More actions
May 23, 2012 at 4:44 pm

#256855

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
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = Upload,
MAX_QUEUE_READERS = 1,
EXECUTE AS 'SPUser'
)
GO
Does anyone have any ideas what I might be doing wrong.
Thanks
Stuart

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

Is your service broker target queue in msdb? The user running the activation procedure will not be impersonated across databases, so if your service broker objects are in Database1, then the activation procedure cannot execute procedures in other databases (like msdb).

I found an article explaining this a little bit better :

http://www.databasejournal.com/features/mssql/article.php/3800181/Security-Context-of-Service-Broker-Internal-Activation.htm

Stuart Pearson SSChasing Mays Points: 637 More actions · Answer 2

Thanks Nils

The Service broker artefacts are NOT in the msdb they're in my database.

I've read the article you linked to and if I understand it right then setting my database to trustworthy and granting my user authentication rights to msdb should resolve the problem.

Unfortunately after doing this it is still not working.

Am I still missing something?

Stuart

Stuart Pearson SSChasing Mays Points: 637 More actions · Answer 3

Sorted.

Needed to set the stored proc that serviced the queue to run as owner and made sure that owners was in the SQL Agent USer role in msdb and also had rights to run the stored procedure in msdb

Also had to digitally sign the stored procedure in my source database and use the certificate to create a user in msdb