March 31, 2019 at 5:06 am
Hi,
I have query on how SQL Server agent jobs get executed. Per my understanding, if the job owner us not sysadmin, then the job runs under agent service acccount. Else under the job owner account by default.
I created a test job that select inserts data between diff DBs in the same instance, with owner as some sysadmin account.
As expected the job ran under service account. But I am surprised the job succeeded.
Because I had not given any permission for the service account in the instance. The login for service account itself does NOT exist.
And service account is NOT administrator in the box as well. Ran profiler and confirmed its the same service account hitting the DB.
Question is... Without any permission in the instance (No login/user) for service account, how it's able to execute the job ?
March 31, 2019 at 4:32 pm
The agent service actually runs under a special service SID account (NT SERVICE\SQLSERVERAGENT). That account has sysadmin rights to SQL Server and allows the agent jobs to run with sysadmin rights.
The only time you need to worry about permissions for the agent service account (assuming domain account) is when you have a job that runs outside the context of SQL Server. For example - SSIS, Powershell, xp_cmdshell jobs run in a separate process that will then utilize the agent service account credentials. If those jobs call back into that instance then the agent service account would need privileges.
With that said - what you should do is create a proxy account with only the permissions needed and use the proxy account for those types of agent jobs.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 31, 2019 at 11:40 pm
Thanks a lot Jeffrey !.
Yes, [NT SERVICE\SQLSERVERAGENT] login is there in the instance, however it was disabled.
The job works even when this account is disabled as well.
However when I dropped the login [NT SERVICE\SQLSERVERAGENT], started getting below error in error log.
Login failed for user '.....\test'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <local machine>]
When I remove "Sysadmin" privileges for the account, it just keeps running. Never completes.
So if I got it right,
When the job owner is a sysadmin in the instance, it runs under the service account set for SQL Server Agent.
However it doesn't matter whether that service account is a login in the instance or not OR what permission it has got, the job will always run under a special service SID account (NT SERVICE\SQLSERVERAGENT) which is sysadmin be default.
Hope my understanding is correct.
Thanks a lot !. Really helped to solve my issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply