Removing the Builtin Administrators - Some Pitfalls to Avoid

Question

Removing the Builtin Administrators - Some Pitfalls to Avoid

Kathi Kellenberger

SSChampion

Points: 11811
More actions
January 4, 2005 at 3:35 pm

#183728

Comments posted to this topic are about the content posted at http://www.sqlserv
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 15 posts - 1 through 15 (of 28 total)

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

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

I find that adding in the 'nt authority\system' account in (as an admin) prior to removing the builtin\admin stops a lot of the pain with 3rd party backup solutions etc.

Steven

Tatsu SSCertifiable Points: 7824 More actions · Answer 2

Here are some more gotchas. Some I have run into and some just came up when I searched for "Builtin Administrators" in the MS Knowledge Base with SQL Server 200 selected as the product.

PRB: SQL Server Full-Text Search Does Not Populate Catalogs

http://support.microsoft.com/default.aspx?scid=kb;en-us;317746

BUG: IsAlive check does not run under the context of the BUILTIN\Administrators account in SQL Server 2000 Enterprise Edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;291255

Be especially careful on clusters. See the following article for more information on this topic in relation to clustered instances of SQL Server:

INF: How to impede Windows NT administrators from administering a clustered instance of SQL Server

http://support.microsoft.com/kb/263712/EN-US/

There were some other articles that came up in the search but I did not include the ones marked "FIX" or "INF" except for the one clustering article. Basically, make sure everything is working right before you remove this group account. That way, if anything breaks, you know exactly why. Then you can put the group back in and research the proper solution.

[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog

Mithrandir SSCrazy Points: 2628 More actions · Answer 3

Do you know where none of my 5 servers (SQL 2000 Std) have the "Via group membership" option in the login propierties screens?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

The "Via group membership" option disappeared after I changed the setting. I thought it was odd as well, but I was just glad the problem was solved!

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Yup. Many configure their agent service to run as the local System account. Although truthfully there has always been an avenue to backup without the need for such rights (Backup Operators and now the user rights in the security policies) but few companies locked down their agents tight as a drum on the security side.

K. Brian Kelley
@kbriankelley

Bill Dillon Valued Member Points: 70 More actions · Answer 6

Interesting article. Now that I accidently deleted the the BUILTIN/Admin, how can I add it back in?

Thanks for the help

Bill

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Log on as an account that is a sysadmin role member, such as the sa account, through Query Analyzer. Execute the following:

EXEC sp_grantlogin 'BUILTIN\Administrators'

EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'

K. Brian Kelley
@kbriankelley

P Jones SSChampion Points: 12361 More actions · Answer 8

I've made a point of removing it from new servers once SQL is installed and the service and agent startup accounts are properly set.

That way it is out before any databases (other than master etc) or users are created so they are created with the rights and logins they need.

currym SSC Eights! Points: 949 More actions · Answer 9

Interesting. Thanks for your comments, especially on the "Through Group Membership" issue.

Steve Bergkamp Old Hand Points: 335 More actions · Answer 10

Kathi:

Great Article. Thanks.

I am interested in your login handling job........

"...The way I accomplished this was by adding the accounts and required access start and end dates to a table. My script grants or removes logins based on the accounts listed and dates. That way, when I need to give access to someone temporarily, which happens frequently, all I have to do is add the account and dates to the table and forget about it. ..."

I think this would be useful for a lot of reasons. Have you ever written this up? I think it would make a great article.

Thanks.

Steve B.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 11

Steve,

I'm glad you liked the article. Sure, I could write an article on this. However, I am very bad in that I am pulling data from system tables. Maybe I can rewrite my proc for SQL 2005 and include both in the article.

Kathi

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Larry Aue Ten Centuries Points: 1027 More actions · Answer 12

"Via Group Membership" is interesting to me. I've wondered if it happens when someone has db_owner rights via a Windows group and then creates an object. You can give a group access to a database, but I bet when they create an object under their owner, then it needs to create the login and uses "via group membership" to denote that you didn't add it yourself.

When you remove BUILTIN\Administrators group and your server/network people want to take the box down for maintenance, do they call you to stop SQL gracefully, or do they just pull the plug?

Rudy Panigas SSChampion Points: 10702 More actions · Answer 13

Has anyone just disabled the Buitlin\Administrators account? That way you can test and if something breaks you can enable it and correct the issue. And if nothing is broken you can just leave it disabled until you actually need it.

Just a thought

Rudy

John M Dennis SSC Enthusiast Points: 148 More actions · Answer 14

Hi Grasshopper,

There is no "Disable" but "Deny" will certainly work for testing purposes. I would eventually delete though or someone may come along a grant access. In our Corp environment there are well over 100 users with some nested privilage under the Builtin\Administrators account so our policy is to setup SQL services with domain accounts, add the DBA group as sysAdmin, and then delete the Builtin\Administrators account after every setup of a new server.

John D