SQL Server agent job question

Question

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?

Answer 1

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.

Answer 2

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

The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose from the following options:

Built-in account. You can choose from a list of the following built-in Windows service accounts: 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.

This account.Lets you specify the Windows domain account in which the SQL Server Agent service runs. 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.

Note :- The Local System account option is provided for backward compatibility only. The Local System account has permissions that SQL Server Agent does not require. Avoid running SQL Server Agent as the Local System account. For improved security, use a Windows domain account with the permissions listed in the following section, "Windows Domain Account Permissions."

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

Connect the SQL Server instance using management studio Right-click on the instance and select

“Reports”—“Standard Reports”—“Schema Changes History”

We get a report of schema changes for all databases from which we can get the user account which was used to delete/drop the database.

If you want to know more about the table info regarding deletion/updation then there is undocumented fn_dump_dblog you can check out Here.

For your further info Here & Here