-2

The SQL job history message column says: The job succeeded. The job was invoked by Schedule 12....

If I disable the windows account that is the job owner, then the job fails with an error mentioning that the account couldn't be found.

For a job that ran successfully, how can I find under what context the job was invoked?

Note: I am not asking under what context the job executed.

variable
  • 3,591
  • 3
  • 28
  • 81

1 Answers1

0

The job was invoked automatically by a schedule that someone created for that job. That schedule has a schedule ID of 12 (per the message you've provided). You can lookup what that schedule is from the system table dbo.sysschedules. You also could just right click on the job in SSMS, click on Properties, then go to the Schedule section to see the schedule details via the UI of SSMS.

Tara Kizer also has this rather complete script on listing out all jobs and their schedules with nicely formatted results, which you might be fine useful.

To expand on my answer, based on some clarification in the comments regarding who invokes the job, the SQL Engine has a scheduler built into it. Presumably that code specifically exists in the SQL Agent service. The SQL Agent service is configured to run under a specific Windows account (either domain or local account) on the host machine to your SQL instance. Therefore one can say that account is the who that invokes all jobs of your SQL instance.

You can equate this similarly to if you wrote a C# (or whatever your procedural language of choice is) program that internally uses scheduling to send an email every hour, and impersonates another user when it runs that specific block of code. Whichever account on the machine of which that program is running is technically the who that invokes the block of code (not the impersonated user) that sends the email every hour. The SQL Agent service is the program, and runs under a specific account on the machine of your SQL instance, in the context of your question.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • Although there is a schedule, my understanding is that the job is invoked under the context of the job owner. For example if you disable the job owner's windows account (say domain1\user1), the job fails and the job history tells you that the job was invoked by domain1\user which the server cannot identify. – variable Dec 16 '21 at 13:00
  • Yes, who it is invoked under is the Job Owner, correct. Sorry when you said "...under what* context the job was invoked?*" I thought you literally meant what invoked it, which is the schedule I mentioned above. – J.D. Dec 16 '21 at 13:03
  • @variable Though going back to your original post, you say "Note: I am not asking under what context the job executed" which is a little confusing too. So there is no distinction between who the job executes under and who invokes it. The job is automatically invoked by a schedule, no specific account (my assumption) is involved for the initial invocation to start the job. This is just scheduling code that is constantly running under the hood of the SQL Engine, and therefore my original answer is technically the answer to your question (with this additional information)... – J.D. Dec 16 '21 at 13:09
  • ...One could argue that the service account which the SQL Agent service runs under is technically the who on invoking all jobs, since the SQL Agent code is ran under that service. – J.D. Dec 16 '21 at 13:10
  • I think that the job is invoked in context of job owner (I think so because if you delete the job owner account from windows then the job won't run). If the job step is TSQL then the step executes in the context of the service account if the job owner is member of sysadmin group. Otherwise the job executes in context of the job owner. – variable Dec 16 '21 at 13:27
  • A job step is executed in a context. So you need to look at the step level. For sysadmin, TSQL uses agent's win login and for other other job steps, Agent service account's windows account. For non-sysadmins, and TSQL agent login using its own win logins and then uses EXECUTE AS and for the other job steps you need to use an Agent proxy. – Tibor Karaszi Dec 16 '21 at 13:30
  • There's a blog post I wrote on that topic many years ago: http://sqlblog.karaszi.com/sql-server-agent-jobs-and-user-contexts/ – Tibor Karaszi Dec 16 '21 at 13:31
  • As the question says - I am not looking for context (user) of job execution. Rather I want to identity the context (user) of job invocation. – variable Dec 16 '21 at 13:33
  • @variable The reason the job fails to run when you delete that windows user is because they are the owner of the job, which is who the job tries to execute under, but it fails because that user doesn't exist for it to execute under. But as far as invoking the job itself, that would occur directly from the SQL Engine (again I'd assume the code specifically lives in the SQL Agent service) and therefore you can say all jobs are invoked by the account of which the SQL Agent service runs under on the host machine. – J.D. Dec 16 '21 at 13:40
  • You want to know who was the job owner at the time it was (attempted) to be executed? – Tibor Karaszi Dec 16 '21 at 14:44
  • @TiborKaraszi My understanding is he's wanting to know who pushed the start job button loosely speaking. Since it's a scheduled job, that who is the SQL Engine, automatically firing off the job. Hopefully my updated answer explains it in the level of detail appropriate for what he is asking. – J.D. Dec 16 '21 at 15:58