(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
QUESTIONS
-
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? -
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 thesa
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 executeUSE [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 onstep_id
= 1, even if someone shuffled the steps around in Object Explorer -> SQL Agent directly? -
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.
Best Answer
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:
ADD SIGNATURE
to sign the stored procedure you wish to callsp_start_job
ALTER CERTIFICATE...REMOVE PRIVATE KEY
to remove the private key from the certificateThen, in MSDB:
msdb
databaseCREATE USER...FROM CERTIFICATE
to create a signed userAUTHENTICATE
to that userEXECUTE
onsp_start_job
to that userIf you've set this up properly, you won't even need to use either
TRUSTWORTHY
orEXECUTE AS
- any user withEXECUTE
access on your stored procedure will be able to execute thesp_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.