Why Does My SQL Server Agent Job Fail on Schedule but Succeed Manually?

SQL Server Administration
1

Hello

I am running into a confusing issue on SQL Server 2019 where a scheduled SQL Server Agent job fails with permission / object errors but runs perfectly fine when executed manually via SSMS. :upside_down_face:

The job calls a stored procedure that handles data processing and logging. When triggered by the schedule; it often fails on simple queries or log insertions that should have no permission issues. :innocent:

I have verified the job step is using the correct proxy account & that the SQL Agent service account has access to all necessary resources. There are no hardcoded file paths / external resources being accessed. :thinking:

It feels like the job runs in a slightly different execution context when scheduled; even though everything looks identical in the configuration. :slightly_smiling_face: Checked SQL Server Agent Job failure - Microsoft Q&A related to this and found it quite informative.I even double-checked some logic like I would when reviewing what is pl sql just to rule out any procedural issues in the stored procedure.

Why would this job behave differently based on how it's triggered? Is there a known difference in environment context, permissions, or isolation for scheduled jobs vs manual execution? :thinking: Any insights or similar experiences would be appreciated.

Thank you !! :slightly_smiling_face:

2

Hello benof,

You should red this article:

Under which user your SQL Agent Job run : SQL Agent Service Account or SQL Agent Job Owner ? – SQLServerCentral

You should execute the code with the agent account, you can excecute the code with another account like mentioned in this article:

EXECUTE AS (Transact-SQL) - SQL Server | Microsoft Learn

If you are running a SQL Server 2019 database you should be aware that you are using T-SQL and not PL/sql (Orcale). Both are SQL but do have there differences.

3

Executed from within SSMS, the execution is done with your rights, and you are probably DBO owner. So there must be some rights you have and the SQL agent hasn't. Could be some rights on a procedure in de master DB.
You could try using EXECUTE as OWNER