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.
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.
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.
It feels like the job runs in a slightly different execution context when scheduled; even though everything looks identical in the configuration. 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? Any insights or similar experiences would be appreciated.
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.
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
When you run the job manually, it’s running under your own login, which usually has way more permissions, so everything works fine. But when it runs on a schedule, SQL Server Agent executes it under the Agent service account (or a proxy), and that account often can’t see the same things — network paths, files, linked servers, or even the same default database. That’s why it suddenly fails “on its own.” Mapped drives are another common trap, since Agent can’t see drives you mapped interactively. The fix is almost always to make sure the Agent job runs under an account that actually has the permissions it needs, or to explicitly grant those permissions to the Agent service/proxy account so the scheduled run matches the manual one.
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.