2 Comments on “SQL Server: Recreating Builtin/administrator account in SQL Server ( 2000 and above)”

  1. There’s a reason BUILTIN\Administrator was removed from the sysadmin role: It’s a security hole.

    From Books Online topic “SQL Server 2008 Security Changes”

    Beginning in SQL Server 2005, significant changes were implemented to make sure that SQL Server is more secure than earlier versions. Changes included a “secure by design, secure by default, and secure in deployment” strategy designed to help protect the server instance and its databases from security attacks.

    SQL Server 2008 introduces additional security improvements. SQL Server 2008 also takes advantage of changes in the latest operating systems from Microsoft, such as the User Account Control (UAC) found in Windows Vista and Windows Server 2008. The following improvements in SQL Server 2008 decrease the surface and attack area for SQL Server and its databases by instituting a policy of “Least Privileged” and increase the separation between the Windows Administrators and the SQL Server administrators:
    •By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.

    Important:

    If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server. Accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group. Failure to include at least one user in the sysadmin role will lock you out of the instance of SQL Server. For more information, see Database Engine Configuration – Account Provisioning and Analysis Services Configuration – Account Provisioning.

    When provisioning a database instance, there are a number of things I’ll do:

    Have an AD Group created with the name [server_name]_[instance_name]_[sysadmins]. The group should live in an OU administered by a small number of trusted people. If, for someon reason, an AD Group is not an option, I’ll create a group on the local server.

    Add the group (AD or local) to the [sysadmins] Server Role on the instance.

    Doing this makes the sa login (and password) irrelevant. Ideally, the sa login should be disabled anyway. It also means that an OU administrator or a local machine administrator can grant sysadmin-level access to the instance just by adding a Windows account to the group.

    It seems like the application you are installing needs to be both a local machine administrator and a sysadmin on the database instance. This seems like it is not following the principle of least privilege. With this level of permissioning, the application could:

    Install additional software on the server

    Remove installed software on the server

    Modify configuration settings

    Drop and create databases, even those that have nothing to do with the application, but are hosted on the same instance

  2. Marc ,

    Thanks a lot .The scope of this blog was to help people come out of a situation where they are not able to log in to the instance .I have had many instances where people fall in this trap .I should have mentioned the BOL part that you have referred to in SQL 2008 BOL ..

    Kind Regards

    abhay

Leave a Reply

Your email address will not be published. Required fields are marked *