Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?

Question

(With apologies in advance since I've been asked to pick up a project written by someone with more skills than me but who I can no longer ping for questions. If it sounds like I may be using terminology inelegantly, it's because I probably don't know any better. This is all taking place in SQL Server 2008+.)

I have a stored procedure, [vpspLaunchAgentJob], that lives on a [ThisAppDB] database that is intended to launch a SQL Agent Job to output flat files into a UNC path. It's basic steps are:

1. Get the the step_id of the first step in the Agent Job (because I've seen instances where the MIN(step_id) for a job may not necessarily be '1'), and proceed to 2.
2. If User has access to launch the job, proceed to 3. (NOTE: "access to launch the job" is controlled on the database-level in this case, and is not part of my issue)
3. If the database has data to go into the file, proceed to 4. (again, database-level and not part of my issue)
4. If the job to be launched is currently running, don't run the job and report back to the user. Else, proceed to 5.
5. Launch the job at MIN(step_id).

HOWEVER The only ways I've seen to do step 1 above (get the job's first step) requires various cross-database queries:

DECLARE /* ... a bunch of variables */
        @JobStartStep   VARCHAR(255),
        @LaunchThisJob  VARCHAR(255) = 'TheJobWeWantToLaunch',
        @OurJobStatus   VARCHAR(50),
        @ErrMsg         VARCHAR(MAX),
        @CrLf           VARCHAR(10) = CHAR(10) + CHAR(13)
        /* ... a bunch more variables */

/* ... Code to check user access to launch ... */

--Get the first Job step
SELECT  @JobStartStep = step.step_name
FROM    msdb.dbo.sysjobs AS job  
INNER JOIN msdb.dbo.sysjobsteps AS step  
    ON  step.job_id = job.job_id  
WHERE   job.name = @LaunchThisJob  
    AND step.step_id = (SELECT  MIN(step.step_id)
                        FROM    msdb.dbo.sysjobs AS job  
                        INNER JOIN msdb.dbo.sysjobsteps AS step  
                            ON  step.job_id = job.job_id  
                        WHERE   job.name = @LaunchThisJob)

/* ... Code to ensure we have data for the Agent Job to do stuff with ... */

--Get the status of the job, don't launch if already running
--Get Full Job running status
SELECT  @OurJobStatus   = CASE  
                            WHEN (ISNULL(sjh.run_status,-1) = -1 
                                    AND sja.start_execution_date IS NULL 
                                    AND sja.stop_execution_date IS NULL)
                                THEN 'Idle' 
                            WHEN (ISNULL(sjh.run_status,-1) = -1 
                                    AND sja.start_execution_date IS NOT NULL 
                                    AND sja.stop_execution_date IS NULL )
                                THEN 'Running' 
                            WHEN ISNULL(sjh.run_status,-1) = 0  
                                THEN 'Failed' 
                            WHEN ISNULL(sjh.run_status,-1) = 1  
                                THEN 'Succeeded' 
                            WHEN ISNULL(sjh.run_status,-1) = 2  
                                THEN 'Retry' 
                            WHEN ISNULL(sjh.run_status,-1) = 3  
                                THEN 'Canceled' 
                            END 
FROM    msdb.dbo.sysjobs AS sj 
JOIN    msdb.dbo.sysjobactivity AS sja 
    ON  sj.job_id = sja.job_id  
JOIN    (SELECT MaxSessionid = MAX(session_id) 
         FROM   msdb.dbo.syssessions) AS ss 
    ON  ss.MaxSessionid = sja.session_id 
LEFT JOIN msdb.dbo.sysjobhistory AS sjh 
    ON  sjh.instance_id = sja.job_history_id
WHERE   sj.name = @LaunchThisJob

--if running, tell the user it's running and don't launch it, otherwise launch it
IF (@OurJobStatus = 'Running')
BEGIN
    SELECT @ErrMsg = @ErrMsg + @CrLf + 'TheJobWeWantToLaunch is currently running. '
                    + 'Wait and try again.';

    RAISERROR(@ErrMsg, 16, 1);
END
ELSE
BEGIN
    EXEC msdb.dbo.sp_start_job 
            @job_name = @LaunchThisJob, 
            @step_name = @JobStartStep
    /* ... Code to tell the user the job is running ... */
END

QUESTIONS

1. In this example, it seems that [ThisAppDB] would need to have 'SET TRUSTWORTHY ON' in place to allow the queries on 'msdb' tables to succeed w/o error. Is this the only way these queries from [ThisAppDB] to [msdb] can succeed?

2. May depend on the answer to the first question, but if it is the only way, I'll need to turn TRUSTWORTHY on for [ThisAppDB]. But we don't have access to the sa account and I would need to define the permissions we need for the account that we can access. What are the minimum permissions one needs to be able to execute USE [msdb] GO ALTER DATABASE [ThisAppDB] SET TRUSTWORTHY ON GO?

3. Is the query to determine the MIN(step_id) for a given job even necessary, or would any given SQL Agent Job always have jobs starting on step_id = 1, even if someone shuffled the steps around in Object Explorer -> SQL Agent directly?

4. If we can get around the TRUSTWORTHY issue by impersonating a user in the proc's definition (...WITH EXECUTE AS OWNER...' or...WITH EXECUTE AS 'UserWhoCanDoLotsaStuff'...`), what would be the minimum permissions that the impersonated user would have to have?

Many thanks for your patience.

Answer 1

Starting an Agent job from a user database is common, and the best way to accomplish it is to use code signing. Books Online describes the concept here.

In your application database:

1. Create a certificate
2. Use ADD SIGNATURE to sign the stored procedure you wish to call sp_start_job
3. Use ALTER CERTIFICATE...REMOVE PRIVATE KEY to remove the private key from the certificate
4. Backup the certificate to a file

Then, in MSDB:

5. Restore the certificate from that file to the msdb database
6. Use CREATE USER...FROM CERTIFICATE to create a signed user
7. Grant AUTHENTICATE to that user
8. Grant EXECUTE on sp_start_job to that user

If you've set this up properly, you won't even need to use either TRUSTWORTHY or EXECUTE AS - any user with EXECUTE access on your stored procedure will be able to execute the sp_start_job, but only from that procedure.

To answer your question #3, no, you won't generally need to search for the name of the job step to start the job. step_id = 1 is usually correct.