Enable SQL Agent Proxies to run T-SQL Job Steps
It is not possible to run a T-SQL job step under a SQL Agent Proxy. This has an impact on how security is managed since one undesirable answer is to grant more rights than originally specified.
If the cumbersome workaround below is used, it makes the deployment of the job more complex. It is possible to run the T-SQL through Operating System or Powershell steps. Often this means deploying a script to the file system that can be run. As follows:
SQLCMD -Q"EXEC StoredProcedure" -E -S Servername
SQLCMD -i \\someplace\script.sql -S sqlserverinstance -E
However, this approach is not symmetrical in behavior to other steps that use proxy accounts.
Upvotes: 86<=-=Dec 27 2010 12:13PM=-=>
Thank you for proposing a new DCR for SQL Agent. We are always looking forward for the customer feedback. We will consider your proposal in one of our future releases.
This functionality seems essential. Why won’t there be a SQL Agent Proxy? In many scenarios there is a Service Account under which all things run and that service account is normally a domain account – which may or may not have permissions to run SQLCMD or Powershell etc.<=-=Jan 25 2011 5:08PM=-=>
Besides, if we run SQLCMD and the Sproc or Query errors out, the job agent steps still are marked successful. There is not robust error handling mechanism using SQLCMD from the Job Agent<=-=Mar 23 2011 1:23PM=-=>
Although it is possible to “simulate a proxy” by granting impersonation rights to a proxy login, this is a more pervasive grant than adding a principal to a proxy. Granting impersonation rights is not restricted to a job step context.<=-=Jul 11 2012 11:49AM=-=>
It would allow different TSQL code to run under the priviledges of the account that they are supposed to run under. Currently we have to run those procs under SQL Agent service, that might have elevated priviledges. That opens up too much priviledges to TSQL code.<=-=May 20 2013 9:25PM=-=>
Wow, opened Dec 2010 and still not fixed!!
I have voted for this as it is an important requirement.
I have a scenario whereby I am wanting to update data on a linked server. The linked server connection is configured to use Windows Authentication. The domain account “svc_dbupdate” has been configured on the target database with permissions on the two tables it needs to update.
I now wish to create a sql agent job that calls a stored procedure and runs with the svc_dbUpdate credential so that it will have the correct permissions to successfully update the target tables. Any other user account would fail.
Given the issue reported here, I cannot do this within the job itself.
Another work around is to create a powershell script that calls the stored procedure and can be called from within the SQL agent jog under a proxy.
This seems unnecessarily complex and makes production configuration and deployment more complex having a .ps1 file to deploy to the sql servers file system.<=-=Jan 13 2016 1:30AM=-=>
5 years, 22 days.
but who’s counting?
Blaž Dakskobler commented
Status update on this one?