Sql-server – Sql Server Agent is part of the sysadmin role even tough Sql Server says it’s not

permissionsSecuritysql serversql-server-agent

I created a Windows account with no extra rights other than domain user and set it as the SQL Server Agent service account with the SQL Server Configuration Manager.

The BoL/MSDN says that this user should be part of the sysadmin role to work but at this point I haven't taken any action to make this happen.

I expected that the account could do nothing but it appears it's … a sysadmin! If you put the following statement in a job step it will confirm this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1 print 'sysadmin'

The job step is a t-sql step with sa as owner so the SQL Server Agent service account is used.

Now for the question. It seems SQL Server Configuration Manager does something that makes this a sysadmin or at least when it is executing the step. BUT at the same time it does not show up in the list of the sysadmins in the GUI.

All the reports or queries I come up with (including SSMS) do not list this Windows account as part of the sysadmin role.

I have the feeling I am overlooking something.

Best Answer

After some reading I think I got it.

"NT SERVICE\SQLSERVERAGENT" is a member of the local group "SQLServerSQLAgentUser$RINUS$MSSQLSERVER". This group has al the right that a sql server agent account needs

"NT SERVICE\SQLSERVERAGENT" is a part of the sql server fixed sysadmin role

"NT SERVICE\SQLSERVERAGENT" can't be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows, but Windows knows they aren't real users. Windows can authenticate them, but they don't have passwords that any human can use. If you run lusrmgr.msc and look at the groups, you will see groups like SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICE\MSSQLSERVER is a member of the group.

The dedicated account that I have created for the agent is set for the windows service

If you connect the dots you will get a chain that explains this. The part thats new for me is that the NT SERVICE\SQLSERVERAGENT can represent the dedicated agent account.

Sources :

Related Question