3

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.

3

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 :

0

I don't have enough reputation to comment but I found this post when researching why my agent wouldn't start and thought this might help someone. The agent on one of my instances wouldn't start after a restart, all of our instances use domain accounts for the agent and none of them show sysadmin when using EXEC sp_helpsrvrolemember 'sysadmin'

The error log gave these errors in order and as a test I gave more and more permissions to the agent account. It was only after I gave each individual permission that the sysadmin error appeared

EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps'

EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access'

EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info'

UPDATE permission was denied on the object 'sysalerts'

INSERT permission was denied on the object 'syssessions'

SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role

After finding this post I discovered that sysadmin must have been removed from the NT SERVICE\SQLAgent$ account at some point as all of my other instances have it from the installation process. After adding it back (and removing the extra permissions I had given domain agent account while troubleshooting) the SQL Server Agent started normally.

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.