THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

SQL Server Agent jobs and user contexts

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/sql-server-agent-jobs-and-user-contexts/

Published Saturday, September 19, 2009 11:57 AM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andy said:

A helpful summary. For the testing of the CmdExec / XP_Cmdshell, I find that running the following is helpful to determine the context that you are running in.

Net Config Workstation

We normally have these issues and then throw some linked servers into the equation which gets us really confused.

February 10, 2010 12:51 PM
 

TiborKaraszi said:

Good tip, Andy. Thanks.

February 11, 2010 2:38 AM
 

Anna said:

Is this article for SQl server 2005, is it still true for 2008 and R2?

June 7, 2012 12:30 AM
 

TiborKaraszi said:

Anna,

This is still true for more recent versions (including 2012).

June 8, 2012 3:14 AM
 

Michael said:

Is there any way to force sql agent tsql jobs to impersonate the owner if it IS a sysadmin role?  I want a sysadmin role to have a mapped login to a linked server and be able to use that in sql agent tsql job steps just as I would if I was running the tsql from a query window.  For security reasons, I want to continue having my linked server login security configured with "no connection will be made" if the user doesn't exist in the mapping list.

In other forums, I have seen phrases like "by default, sql agent jobs will run as the service account if the owner is a sysadmin fixed role"...which leads me to believe there is some advanced option to change that behavior and force impersonation...

January 8, 2014 10:30 AM
 

TiborKaraszi said:

AFAIK, no. I can think about two workarounds:

A: Use CmdExec step instead and call SQLCMD.EXE to execute your commands. You can now use an Agent Proxy for your Windows login, but as that login changes password, you have to modify the underlying Credential. Or if you want to use a SQL Server login, then you would expose login name and pwd in the jobstep (as command switches for SQLCMD.EXE).

B: Use TSQL jobstep, but add an EXECUTE AS LOGIN = in the beginning of your TSQL code. I don't know how well an impersonated login plays with linked servers, though... Since you are sysadmin (your prereq's), you are allowed to EXECUTE AS LOGIN for anyone.

January 8, 2014 11:01 AM
 

Michael said:

I'll try option B and see if that works...  will update shortly.

Thanks for the quick response!

January 8, 2014 11:07 AM
 

Michael said:

Holy cow, you are awesome!  "Execute as" worked perfect!  My job step was just running an exec storedprocedure, but now I've prepended the execute as just before it execs and it fires off perfectly!

THANK YOU!

January 8, 2014 11:13 AM
 

jon said:

I have non-syadmin account having SQLagentoperator role in SQL server. My application wants to use this account to create SSIS job with different owner through automated process.  I dont want to grant sysadmin rights to this account.  

ANy thoughts.

February 18, 2015 1:51 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement