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)
Best Answer
The probable reason I can think is when u execute manually it might be because you are executing with login with privileges or access to execute that proc.
However sql agent job might be using the account assigned to run sql agent services. Hence you see the error for login failed.
Probably check the error log when failure happens to get more details on that login failure message