I have 2 SQL servers: A & B. On A, I have a stored proc that processes some data from B and then dumps the results into a table on A. I need to schedule a job to run this proc.
According to http://msdn.microsoft.com/en-us/library/ms189064(SQL.105).aspx, it's the Job Owner that gets used when running the job. (Can't set a "Run as" on the job step for TSQL step/job.)
How the network people have set these machines / security up for is to use a third server, C, with centralised logins stored on that. So my login is: SERVERC\SEAN. This login has access to both DB servers, A & B. The proc runs fine if I execute when logged in and connected to Server A.
So I set the job owner as SERVERC\SEAN, but when the job runs, the error message is: "Executed as user: NT AUTHORITY\NETWORK SERVICE. Login failed for user SERVERC\SERVERA$"
Why is it running as Network Service, and some weird login combination, instead of as the job owner, as instructed? How do I get this to work?
Thanks
Check out this post, it should do what you want it to.
Editing to show most helpful link http://social.msdn.microsoft.com/Forums/en/sqltools/thread/54dff132-b9c9-4753-b0c5-2134bf7f4327