SQL Job step run as different sql account

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]
SELECT 
       ss.[Ref_ID]
      ,mm.[studentID]
      ,mm.[studentPersonID]
      ,mm.[studentFirstname]
  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.

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.

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

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