(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:
- 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.
- 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)
- If the database has data to go into the file, proceed to 4. (again, database-level and not part of my issue)
- If the job to be launched is currently running, don't run the job and report back to the user. Else, proceed to 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
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
May depend on the answer to the first question, but if it is the only way, I'll need to turn
[ThisAppDB]. But we don't have access to the
saaccount 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?
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?
If we can get around the
TRUSTWORTHYissue 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.