0

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)
1

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

1
  • Yes, the sql agent services is using another login to run the job but when I looked at the properties of that login, sysadmin is checked but I am still getting same error.
    – user216531
    Oct 2 '20 at 15:22
1

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.

3
  • sql agent job is using another login and when looked at the properties, server role says its a sysadmin but I am still getting the error. Is there a way I can execute the job using my login?
    – user216531
    Oct 2 '20 at 15:27
  • Please be more precise. Is the job owned by a non sysadmin? Or is the job step defined as a different user? Pls be as exact and detailed as possible. Oct 2 '20 at 17:56
  • @user216531 Check out this article: docs.microsoft.com/en-us/sql/t-sql/statements/… Oct 6 '20 at 17:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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