Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 at 23:02

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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 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. –  Shawn Melton Mar 17 at 5:48

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

share|improve this answer
    
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 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 at 22:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.