0

I created a SQL Server job, in which:

  • My user is set as the owner of the job
  • No user is specified for run as at any step.

The job failed with the error:

'EXECUTE AS LOGIN' failed for the requested login <my_username_here>

When I changed the owner of the job to another user with more privileges, the job worked.

So, it seems like the job runs under the user set as the owner - but I've read several questions here (1, 2) or in SO discussing "who is the user running the job", and none mentioned that.

So I wonder what happened in my case?

Improve this question

1 Answer 1

Reset to default
1

So, it seems like the job runs under the user set as the owner...

Yes, that is the account used (internally) to execute the code in the steps of the job, by default.

but I've read several questions here (1, 2) or in SO discussing "who is the user running the job", and none mentioned that.

The first two questions you've linked are unrelated. The first one is asking about finding who manually kicked off a SQL Job. The second one is asking who does the SQL Agent service account run as (externally, in Windows). The third question you linked is relevant but incomplete in information.

It is important to choose the proper owner of your SQL Jobs. Generally that won't be an actual user, such as yourself, but should be generic dedicated Logins. I have a single, properly provisioned dedicated Windows account, which I use for the owner of all of my SQL Jobs. That way, I don't have to worry about jobs failing if a specific person's account's permissions change, gets disabled (e.g. they leave the company), gets locked out (from password expiring, etc).

Improve this answer
3
  • Thanks! for the good guidance as well! One tangent: "The first one is asking about finding who manually kicked off a SQL Job" - wouldn't that always be the user set as the owner? for example, if user "a" is set as the owner, and user "b" is trying to execute the job - than given that the job always runs under the owner, we should always see that "a" executed the job - is that wrong?
    – HeyJude
    Jan 19 at 15:19
  • @HeyJude No problem! Depends on the context of where you're looking. But the question that the person was asking was "how to find user b when they manually run a job?". So it was unrelated to your question. According to the answers on that post, the sysjobhistory table does log in the message column who user b is when it's manually kicked off (but I don't know enough about the sysjobhistory table to corroborate that answer - though I believe it to be accurate).
    – J.D.
    Jan 19 at 15:38
  • 1
    @HeyJude To come full circle on that first question you linked, it appears that the sysjobhistory table does log user b - the user who manually executed the job, in addition to the owner, in the message column, based on my testing.
    – J.D.
    Jan 19 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.