1

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?

  • Did you checked 'SQL Server configuration Manager'. Just double click over 'Sql server agent'. Go to 'log on' tab that will show your running account of sql server agent. – Md Haidar Ali Khan May 17 '17 at 11:27
3

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.

  • Thanks for the BOL reference. That is what my question is for my 1st question. i.e. even though I don't have SQL Server agent account in SQL Server logins, how it is executing the job? – SQLPRODDBA May 17 '17 at 13:29
  • 1
    But you said yourself, the account running SQL and the Agent is the same, so it's the god account in SQL. Look up the NT Service virtual accounts. – Nic May 17 '17 at 13:39
  • @SQLPRODDBA what account your services are running under? – SqlWorldWide May 17 '17 at 13:45
  • @Nic: Do you mean checking membership of default NT accounts that gets created when installing SQL Server? If yes, I have already checked locally on server and I didn't see SQL Server agent account part of that. Both SQL Server and SQL Server agent is running under a domain account that we created specially for SQL server. Also, as I said xp_logininfo 'SQL Server Agent account', 'all' does not show account part of any group/individual login. – SQLPRODDBA May 17 '17 at 13:49
  • @SQLPRODDBA edited my answer. What you see when you run xp_logininfo 'NT SERVICE\SQLSERVERAGENT' – SqlWorldWide May 17 '17 at 14:13
-1

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

  • Thank you for your answer but it is not related to the questions I asked. – SQLPRODDBA May 17 '17 at 13:33
  • @SQLPRODDBA,Could you update execting this query "exec xp_logininfo; Go . what you are getting the SQL ServerAgent Login. Is it NT Service or ther. – Md Haidar Ali Khan May 17 '17 at 13:40
  • @SQLPRODDBA,I think at first time You just asked about "Sql ServerAgent" mandatory login? why it is necessary? Hope Soon update as per your edit statement. – Md Haidar Ali Khan May 17 '17 at 13:44
  • Already did that and mentioned in my original question that I don't get any output for it. – SQLPRODDBA May 17 '17 at 13:44
  • @SQLPRODDBA,Could you update along with screen shot. I Would remind one again you are asking 'SQL server login'. So could you check through this query 'select * from master.sys.sql_logins; Go How many sql_logins are there. – Md Haidar Ali Khan May 17 '17 at 13:46

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.