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.