1

I have a T-SQL agent job that I've created that adds steps to itself depending on a query of the MSDB database.

I am able to run the query outside of the agent, inside a query editor window, but when I try to run the query as a job I get the following errors:

Executed as user: [user with sysadmin rights for testing]. The SELECT permission was denied on the object 'sysjobsteps' database 'msdb', schema 'dbo'. [SQLSTATE 42000] (error 229) The EXECUTE permission was denied on the object 'sp_update_jobstep', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed

As I said in the error, I've tried this with a user with LUA, a user with the sysadmin role over the server, a user with owner role on the msdb database, and I've tried explicitly granting the needed permissions on the msdb database.

What have I done wrong in the creation of the agent job?

Let me know if you need more info.

UPDATE: I removed the Run As: user from the Advanced tab of the step and the job ran successfully. Why doesn't that work as I expected it to?

  • 3
    that adds steps to itself - can you explain why? Something doesn't smell right. – Aaron Bertrand May 29 '15 at 19:51
  • Can you clearify what you mean here "I have a T-SQL agent job that I've created that adds steps to itself depending on a query of the MSDB database." ????? – Zane May 29 '15 at 19:52
  • I have a job that has a separate step to back up each database. The first step in the job determines if any databases have been added or removed and adds or removes a step from the job to accommodate it. It was a bit hacked because the maintenance plan wizard was having similar issues. I've since abandoned this tactic and engineered something similar with powershell, which works much better in this environment. – Snowburnt May 30 '15 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.