I'm curious how to set up a SQL job step to be executed as a different SQL login account. It appears I need to set up a New Proxy Account which requires an existing credential. When I create a credential my only option is to use a Windows Login credential.

The job I'm attempting to run is below. There are other additional statements but when I set the job step to run as the SQL login it fails.

insert into [dbo].[TableA]
  FROM [dbo].[TableB] mm
  left outer join [dbo].[TableC] ss on ss.parentPersonID=mm.parentPersonID and mm.studentPersonID = ss.studentPersonID
  where ss.Ref_ID is not null;

When this is run through a SQL Job Step it fails.

Executed as user: an_admin_account. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed.

I'm unclear as to why it's trying to access a remote server when all of these tables exists on the local db.

  • A SQL job step already has a connection to the SQL instance. So you need to change the existing security context, rather than making a new connection with a password. If you want to create a new connection, you could use a sqlcmd command, or PowerShell script, which could then use a SQL login and password as parameters within the script. But you should consider what you need to do, and whether a stored proc with its own context will suffice. – Rob Farley Mar 15 '14 at 23:02

If you are configuring a T-SQL job step go to the Advanced Page and configure the "Run as user" to the login of your choice. enter image description here

If you are working with other job step types like PowerShell it will require a proxy account to be configured.

  • I've tried this but now the job fails saying " Access to the remote server is denied because the current security context is not trusted". However if I run the script while logged in as the user, it is able to run it just fine. There is not linked server in the query so I don't understand why it's referring to a remote server. Any thoughts? – Geoff Dawdy Mar 15 '14 at 17:20
  • If you could add to your question what the job is trying to do and exact error would help us point you to a solution. – user507 Mar 17 '14 at 5:48
  • In my case it ignored the setting. Using SQL 2012. – Miguel Sv May 2 '19 at 10:37

Use EXECUTE AS at the start, or create a stored procedure which executes in a particular context.

  • This might be bad. If you want call a stored procedure with a restricted user it could REVERT to sysadmin (or job owner) privileges. Workaround would be using a cookie. – Jens W. Mar 15 '14 at 17:10
  • A stored procedure that has been created using the EXECUTE AS option will handle the privilege escalation, and not allow someone to change part way through. If there's no stored proc available, then impersonating a different login will do the trick, like with EXECUTE AS at the start of the script. – Rob Farley Mar 15 '14 at 22:59

Your Answer

