February 28, 2012 at 7:57 am
I have a job that runs a tsql script to monitor for hung ssis jobs. When a job is found I send mail using sp_send_dbmail and include a query. This query is run against msdb to return the job info.
Our policy is that the agent account does not get specific permissions but to run this query I need data reader on MSDB. I could do this with an execute as but again that means giving impersonate to the agent account.
is there any way around this like a proxy for running TSQL job steps?
thanks
February 28, 2012 at 9:10 am
Some possible options:
- If you're just reading base tables in msdb in the dbo schema and not catalog or system views then can you create a stored procedure in msdb that leverages ownership chaining and just grant the sql server agent service account public in msdb and EXEC permission on the proc?
- If the above option will not work then maybe a certificate signed stored procedure in msdb will work.
edit: "sql server agent service account" not "sql server account"
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2012 at 12:49 pm
OK, found a very simple solution to this.
I was confused originally since I thought jobs ran under the context of the job owner and my job was clearly running as the Agent service account.
What I found out is that when the job owner is part of the sysadmin server role the job will, by default, run under the agent service account.
I simply changed the owner to an appropriate account and it worked fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply