1

What SQL Server login does the SQL server agent use to log in to the database instance?

I am running the SQL server agent as the built in local system account.

I have created a SQL agent job which has one step. That step inserts the value of SYSTEM_USER into a table.

When i Run the job and then select from the table when the job is complete, I can see the value of SYSTEM_USER was DOMAIN\SERVERNAME$ so it seems that is what the agent is logging into the instance as.

So why can the agent log in as DOMAIN\SERVERNAME$ when it is not an SQL Login?

What privileges does this account have?

3

SERVERNAME$ is a machine account assigned to the server in Active Directory. That account maps locally as NT AUTHORITY\System. You'll see in the SQL Server security there is typically an account named NT AUTHORITY\System listed - this is how the SQL Server Agent is getting access when it is configured to run as Local System.

Microsoft Docs has a great set of details that help with choosing how to configure the service account for SQL Server Agent. It states the following:

Local System account. The name of this account is NT AUTHORITY\System. It is a powerful account that has unrestricted access to all local system resources. It is a member of the Windows Administrators group on the local computer, and is therefore a member of the SQL Server sysadmin fixed server role

4

The SQL Agent uses whatever is assigned as the login on the Windows Service.
To access Windows services and see, click the start menu and type 'Services'.

Locate the SQL Agent and view the properties.
If the account is the Local System, then it is actually using the credentials of the machine. You confirmed that already when you saw that the DOMAIN\SERVERNAME$ was returned.

(That is the account for the machine that it's hosted on. All PC's have a local system account and it gets added when SQL is installed. I believe the system account inherits sysadmin privileges which is why you probably don't want to leave it as the default. NT AUTHORITY\SYSTEM I believe is how it gets added to the logins in SSMS.)

If you want to assign different permissions which is a good idea, you can use a different login, usually a service account or master service account.

Alternatively create a credential in SQL server assigned to an account that has permissions. Then create a proxy in the SQL Agent and you can use that to run the job.

According to MS best practices is to always assign the login credentials for the service accounts using SQL Configuration Manager. While I've never run into a problem doing it other ways, if MS says so, it's probably correct.

1

In addition to the great answers above, I also found that if a job is TSQL type, it will run as the job owner, unless explicitly chosen to run as a given database user in the Advanced -> Run as User field

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.