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)
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
Please add more details.
I assume T-SQL job step type here. If you use something else (cmdexec, SSIS, Powershell), then let us know.
Agent service account is always sysadmin with the connection it uses to SQL Server.
A job owned by a sysadmin will result in that Agent by default will execute as itself, i.e., sysadmin.
A sysadmin can do everything, everywhere.
So, clearly, your job isn't either owned by a sysadmin or you have configured the job step to execute as somebody who isn't sysadmin. Or perhaps you have some EXECUTE AS command in whatever T-SQL you execute.
Check what the default database for that login is, and on the job, in which database the job is set to run. If it is a member of sysadmin, the only way it can fail to login is if the login is disabled, if the default database or database specified in the connection attempt does not exist (or is not online, or set to single user), or if rights to the default or specified database have specifically been revoked.
