Removing the Builtin Administrators - Some Pitfalls to Avoid

  • 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

  • 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

  • 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

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

  • 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

  • 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

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

    Thanks for the help

    Bill

     

  • 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

  • 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.

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

     

  • 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.

  • 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

  • "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?

  • 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

  • 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

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

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