I have a stored procedure that runs fine when executed manually but fails in scheduled job. The error I receive is: Executed as user: \USER-a. Login failed for user '\USER-a'. [SQLSTATE 28000] (Error 18456). The step failed.

My other stored procedures runs fine when scheduled. The only stored procedures that fails are the ones that are written in this form.

SET @QUERY = 'SELECT * from abc'

EXEC(@QUERY)