Removing BUILTIN\Administrators

Question

Removing BUILTIN\Administrators

HBK_4700

SSCrazy Eights

Points: 8932
More actions
September 2, 2010 at 7:29 am

#157179

Hi,
We had setup a SQL 2005 cluster active/passive server. We are going to use this one for production.
Now we want to remove the BUILTIN/Administrators from SQL Logins.
As far I know we need to add the Cluster service account to SQL LOGINS then we are good to remove the builtin/admin.
Is there any other things need to be taken care?
Is it possible to add BUILTIN/Administrators again to SQL login?
Please advice

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Instead of removing the Group, you can remove the sysadmin privilege from the BUILTIN\Administrators group.

But before doing so make sure that you know the sa password or you have atleast one sysadmin user who is not part of the BUILTIN\Administrators.

But It's always a best practice to remove BUILTIN\Administrators group from the SQL server.

Thank You,

Best Regards,

SQLBuddy

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

we want to remove the BUILTIN/Administrator after adding the Window group to SQL Logins to administer the SQL Server

D0 We need server failover/failback the cluster to ensure everything is working fine.

http://support.microsoft.com/kb/263712

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Failing over the resources would confirm if the change has gone well with SQL Server or not

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

Thanks for your replies.

I have successfully removed BUILTIN/Administrator after adding the group to SQL Login that used to administer the SQL Server.

Our server is SQL Server 2005 cluster server active/passive

What authentication is required in this scenario.

Presently in mixed mode, can I change it to windows authentication ?

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

laddu4700 (9/7/2010)
Thanks for your replies.
I have successfully removed BUILTIN/Administrator after adding the group to SQL Login that used to administer the SQL Server.
Our server is SQL Server 2005 cluster server active/passive
What authentication is required in this scenario.
Presently in mixed mode, can I change it to windows authentication ?

There are very very few situations where you can move from mixed to pure windows authentication. I wouldn't recommend it. For one, most applications and ODBC sources connect via username/password, not kerberos. Second, if your domain controller goes down, has an error, someone deletes/disables your one admin account, etc...well you're up the creek without a paddle.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 6

laddu4700 (9/7/2010)
Presently in mixed mode, can I change it to windows authentication ?

If you do see that all of your applications are using Windows Authentication and not SQL you can change it to Windows Authentication. The alternate that I do sometimes if I am Windows Auth only and utilize domain accounts, is to create a local account and add that account to SQL Server. That will then allow you to get into the instance when you cannot authenticate a Windows domain account. As well you can just disable the local Windows account when you don't need to use it.

Derrick Smith
Second, if your domain controller goes down, has an error, someone deletes/disables your one admin account, etc...well you're up the creek without a paddle.

On most occassions if my domain controllers go down, the SQL Server instances are the least of my problems at that moment. 😀

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton