What permissions do you need to run jobs via SQL Agent?

Question

Post reply

What permissions do you need to run jobs via SQL Agent?

cristian 26986

SSC Veteran

Points: 283
More actions
April 9, 2018 at 3:07 pm

#330260

I've googled my issue all weekend, but can't seem to solve the problem
So basically, I have a query, when I run it manually in a query window, it works fine and my job executes, resulting in an email being sent. However, when I set up a schedule, I get the following error:
If you can't read it, the error message says,
"The job failed. The job was invoked by Schedule 9. The last step to run was step 1."
Then underneath it says,
"Executed as user 'adminName(not sa)'. Failed to initialize sqlcmd library with error number -2147467259 (SQLSTATE 42000) (Error 22050). The step failed"
So I log in with user sa, and apparently this user needs to have SQL Agent permissions in the msdb database. According to this screenshot, sa is the owner:

So since sa is the owner, it would mean it has all the SQL Agent related permissions, right? Furthermore, the code I'm using executes from the CompanyDB, so I think perhaps this may be the issue. If I look at properties, under sa, I get this window:

So the CompanyDB is unchecked for user sa, so in order to allow sa to query CompanyDB in SQL Agent, what permissions would it need? I'm assuming this is the issue, but I'm not really sure, I can't seem to figure it out, any help is greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

Can you run the job by using the Service Account that is running the SQL Agent?

=======================================================================

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Who owns the job? In your error message, it says:
"Executed as user 'adminName(not sa)'.
I'm not real sure what the focus is on sa if it's executing as adminName.

If the job is failing on a t-sql job step then if the owner is a sysadmin, it executes under the SQL Server Agent service account. If the job is owned by a non-sysadmin, it executes under the security context of the owner of the job.

Sue

cristian 26986 SSC Veteran Points: 283 More actions · Answer 3

IrfanHyd - Monday, April 9, 2018 4:29 PM
Can you run the job by using the Service Account that is running the SQL Agent?

I don't know which one is the Service Account

I tried opening Reporting Services Configuration Manager but I can't seem to be able to connect, currently have a ticket with our IT to help me with this part

I barely started this whole project on Friday, so excuse me if I sound idiotic

cristian 26986 SSC Veteran Points: 283 More actions · Answer 4

Sue_H - Monday, April 9, 2018 5:26 PM
Who owns the job? In your error message, it says:
"Executed as user 'adminName(not sa)'.
I'm not real sure what the focus is on sa if it's executing as adminName.
If the job is failing on a t-sql job step then if the owner is a sysadmin, it executes under the SQL Server Agent service account. If the job is owned by a non-sysadmin, it executes under the security context of the owner of the job.
Sue

Okay so the owner of the job is sa, and it is being executed by an Administrator username. Maybe that Admin account doesn't have the right permissions? Even when I run the job by clicking on it, it says "Invoked by sa", but executed by a that same Admin user. Maybe that's the issue, although definitely going to need help from our IT, just trying to narrow down the issue for them

Sue_H SSC Guru Points: 90860 More actions · Answer 5

cristian 26986 - Monday, April 9, 2018 5:55 PM
Okay so the owner of the job is sa, and it is being executed by an Administrator username. Maybe that Admin account doesn't have the right permissions? Even when I run the job by clicking on it, it says "Invoked by sa", but executed by a that same Admin user. Maybe that's the issue, although definitely going to need help from our IT, just trying to narrow down the issue for them

When you log in as sa and execute a job, that is why it says invoked by sa - it was invoked by you when you logged into SQL Server as sa. If it's scheduled, it will say invoked by schedule <schedule name>
To see the service account, you would use SQL Server Configuration Manager rather than the Report Server Configuration Manager.
If you are a sysadmin, run the query as 'adminName(not sa)' to test the permissions for the query.

Sue

cristian 26986 SSC Veteran Points: 283 More actions · Answer 6

So just to wrap this up, out IT figured it out, but unfortunately, I have no idea what they did as they worked on it remotely. Here'e the message they left to close out my ticket:

After trying to get it to run under other accounts with no luck. On the server there is a service for the Agent that runs the jobs. Its called"SQL Server Agent (E2SQL)" I updated that account to use a different windows account and it appears to be running now.

sgmunson SSC Guru Points: 110639 More actions · Answer 7

cristian 26986 - Thursday, April 12, 2018 10:11 AM
So just to wrap this up, out IT figured it out, but unfortunately, I have no idea what they did as they worked on it remotely. Here'e the message they left to close out my ticket:
After trying to get it to run under other accounts with no luck. On the server there is a service for the Agent that runs the jobs. Its called"SQL Server Agent (E2SQL)" I updated that account to use a different windows account and it appears to be running now.

So basically, they changed the service account that the SQL Server Agent runs under, and solved the problem. They needed an account that had the necessary permissions to do what the job needed to accomplish, as Agent jobs run under the security context of that service account. In the future, if a job fails, it may just mean that the account in use needs to be added to the permissions for whatever it is within that job that it can't otherwise access.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

dmbaker SSCertifiable Points: 5089 More actions · Answer 8

So, if you look at Job Steps of your job, what does the "Run As" say? That's the account the job will run as. That account will need permissions to access the resources you're trying to access. If your IT account changed the service account that the SQL Server Agent runs under then I think they did the wrong thing.

This sounds like a case where SQL Server Agent's "proxy" thing would be good to use, maybe? Set up a new Windows account, grant that account appropriate access to your report/database/whatever (it should not need SA-level privileges to run a report), create a new SQL Agent proxy and assign that new account as a principal of the proxy (or, use the service account that your IT folks used since it works), then set the proxy as the "Run As" in your job step.

Setting up proxies are a hassle, but it sounds like in your case it actually might be a good thing to do, rather than changing the SQL Agent service account -- who knows what-all that will break?