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?