May 3, 2006 at 11:58 am
I inherited a SQL Server which was using the Local Administrators Account to log on to the MSSQLServer Service and the SQLServerAgent Service. This SQL Server also had the Local Administrators Account and the Windows 2003 BuiltIn Administrators Group as SQL Server Logins. I have since created a Network Account (produser) and assigned it to the Local Windows 2003 Administrators Group. I created a SQL Server Login (produser) with the Network Account and assigned it to the 'sysadmin' Fixed Server Role. I have changed the SQL Services so they now log on with the Network Account (produser). I needed the Network Account so I could be able to use the email features of MS Outlook and Exchange. Everything appears to be working fine.
My question is; would it be okay for me to delete the Local Administrators Account and the Windows 2003 BuiltIn Administrators Group from the SQL Logins?
We are running SQL Server 2000 Standard Edition (sp3) on Windows 2003.
Thanks in advance, Kevin
May 3, 2006 at 12:47 pm
It depends. It sounds like you have the basic bases covered. Now the outstanding question is whether there are other users that were members of the admin groups that will lose their access once you drop the logins. You should determine the affected accounts and figure out what kind of access they really need.
May 3, 2006 at 11:53 pm
You can find more on this subject here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=185347#bm185575
Andy
May 4, 2006 at 12:03 am
kevinsql7,
Be careful when dropping the BuiltIn\Administrators Group from the SQL Server. Reason I say this is the the Account that is used to Start the SQL Server Agent requires SA rights on the SQL Server. By default the account that starts the SQL Server Agent is a member of this group on the SQL Server. If this group has not been touched since installation, you will notice that this group has 'System Administrator' rights!
May 4, 2006 at 6:00 am
As David mentioned (via the link to the other discussion), as long as you add 'NT Authority\System' as a Windows login, assigned to the sysadmin role, you shouldn't have any problems. I have removed Builtin\Adminstrators from all of my servers. I also use full text search without any problems. Of course, I also run all of my SQL Server services with a domain account.
You can read about the LocalSystem account (and other service accounts) here:
May 4, 2006 at 9:36 am
The recommendation is remove these accounts unless they need access. I have done it both ways. There are times I want the administrator having sysadmin privileges, like when I'm on vacation. I want him/her to be able to work on SQL under my instruction if necessary.
If it's not a large group, however, you may want to explicitly create a DBA group on Windows AD and make it a sysadmin, then remove builtin/admins.
August 7, 2006 at 4:09 am
Hi all,
Sorry for reviving this old thread but my questions are directly related to the comments above.
With the help of all the articles and discussions I was also able to change both my SQL service accounts to use domain accounts and I have also successfully removed the builtin\administrators group. BUT I see the folloing error in eventviewer:
Source: MSSQLServer EventID: 19011 Description: SuperSocket info: (SpnRegister) : Error 8344
According to MS (http://support.microsoft.com/kb/811889/) my domain account used to start the MSSQLSERVER service, does not have the required access so register/create the SPN and that I need to create one manually?! No one above or in the other discussions mentioned this? Did any of you have to do this? Is there a way of getting past this?
My setup:
Windows 2003
MSSQL 2000 SP4
Merge and Transactional Replication
Both domain accounts used for the SQL services have logins with sa rights
Currently both domain accts are part of the local administrators group, but after getting the problem above sorted, I will remove them and scale down the permissions to what they need.
Any comments/suggestions EXTREMELY WELCOME!
TX
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply