SQL Server Performance Forum – Threads Archive

SQL Server Agent Account

I am trying to remove the BUILTIN/Administrators Login and setup SQL Server Agent Service to run under a domain user account within the local Windows 2000 Administrators group. This is to prevent all of the "other" Windows Administrators from also being SQL server System Administrators. I am having a problem with implementing a domain user account to run the SQL Server Agent Service. I have created an account OCIO/SQLServerAgent, included it as a member of the Administrators group on the Windows 2000 server, added the account to SQL Server as a System Administrator, and setup the SQL Server Agent service to use this account. All of the jobs are owned by sa. I redid everything leaving the BUILTIN/Administrators Login account in SQL Server and it works fine. When I remove/delete the BUILTIN/Administrators account, stop and start the service, the jobs fail with: Executed as user: OCIOSQLServerAgent. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. as viewed from the job history details. There is NO record in the Database Maintenance Plan History for this error.?. Any thoughts or things to try? There must be a difference between being the BUILTIN/Adiministrator and being a member of the local Admiistrators group. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks! Gary

Have you restarted SQLAgent after setting the new user account? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Well it’s not enough to run SQL server agent on local admin rights. This account must have a couple permissions in order to work fine. More info: http://support.microsoft.com/default.aspx?scid=kb;en-us;283811 Cheers

Satya, Yes, I have stopped and restarted the service. Zvidas, Thanks for the URL. Good info. I have been trying to look for this right up. I will check these things and try them out.

Im about to remove this builtin/admin could you clarify my plan 1…Create a new domaindbstart account on Windows NT
In SQL Security, Logins, properties, server roles…tick just the System adminstrators only….databases nothing ticked…do i have to tick all the server roles…do i need to tick all the databases just like the builtinadmin.? I been reading about this….
2. Add a new SQL Security Logins
called NT AuthoritySystem
In SQL Security Logins, properties …set this to System adminstrators only.
Should i tick all databases to yes in here. Not sure what the NT Authority System are. Once i done all my new accounts. 3. Go to Security, Server Roles, System Administrators, properties and
remove builtin/admin 4. Restart SQL Server…….. Thanks

1. Ensure the created login has SA option ticked and no need to tick for any other databases or server roles.
2. As long as SysAdmin is ticked, no need for others.
3. Yes
4. If you’ve removed the builtinadmins then no need to start the SQL server, it is required if you are changing the SQL Server service account. In this case if you’re using local Administrator then yes make sure to use a domain/local account to finish the task. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>