Sql-server – SQL Server agent job question

sql serversql-server-2012sql-server-agent

Scenario:

I am trying to understand a strange situation. I have a SQL Server 2012 having DB engine and SQL Server agent configured using same domain account. That domain account isn't added to SQL Server logins. Checked using xp_logininfo 'Domain account', 'all' and didn't get any output. I have a SQL server job which updates a table in a DB. That job is running fine. Till now I was under the impression that SQL server agent account has to be defined in SQL Server login in-order to run the job. However, in this case the job was running fine even though SQL Server agent login is not defined in SQL Server logins. So trying to understand how it is possible. Also, since the job updates a table don't know which user/login it uses to update the table. As a test, I changed the job owner to 'sa' and non 'sa' login in both the cases the job is working fine.

So my questions are:

  1. Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job? If yes, then how come my update job is running even though there is no login present for SQL Server agent account in SQL Server logins. If no, how it internally works? Like which account it uses to start the SQL job.

  2. What user/login my update job is using in order to update the table?

Best Answer

1. Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job?

See this and this.

The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.

As for the account that you used to run the services, this is complicated and has changed in each version of SQL Server.The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.

There are permission in the operating system that an agent account gets by default. Details here.

As per books online:

The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles:

The account must be a member of the sysadmin fixed server role.

To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.

In all Windows versions, permission to log on as a service (SeServiceLogonRight)

But it should not be a member of local administration group.

We recommend choosing a Windows user account that is not a member of the Windows Administrators group. However, there are limitations for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group

2. What user/login my update job is using in order to update the table?

Reference: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/

Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.

The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.