Installing SQL Server 2005 on Vista Without Having to Provision Windows Users as Administrators

A lot of questions get asked on the MSDN SQL Forums about Installing SQL Server on Vista and problems with Windows Accounts in the SysAdmin role due to the UAC.  The Provisioning Tool for Vista that came with Service Pack 2 allows you to Provision a user to function as a SysAdmin inside of SQL Server. However, while answering a question on the forums that I could not seem to break through the problem without actually installing Vista and SQL Server on Vista, I found out that it is not necessary to Provision a User Account for it to be a SysAdmin inside of SQL Server.

I have always followed best practice installations for installing SQL Server whether it be a simple demo/test install or a full on production SQL Server my business will use.  That being what it is, I always use SQL Authentication because I like having the sa user with an extremely complex password for those holy crap moments when I need the extra backdoor to SQL Server in a pinch.  One of the first things I always do is remove Builtin\Administrators from SQL.  If you do this, you have to explicitly add a Windows Account to SQL Server for it to login to SQL, and you have to explicitly assign the account to the SysAdmin fixed Server Role.  Doing this removes the need to Provision the User inside of SQL Server since the account does not elevate its OS permissions as an Builtin\Administrator to access SQL.

If you are following Microsoft Security Best Practices, you shouldn't have to provision any windows accounts for them to access SQL Server.  My recommendation to anyone using Vista would be to remove the Builtin\Administrators Group from SQL Server, and to explicitly add the correct accounts to SQL as a SysAdmin thereby eliminating the need for escalating permissions.  I am surprised that Microsoft took the route of providing a provisioning tool to do what is a polar opposite of the recommendations that have been made since SQL Server 2000.  The removal of Builtin\Administrators was covered in the following articles:

SQL Server 2000 Operations Guide: Security Administration (search for Builtin\Administrators)

SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

What is most interesting is that the SQL Express Blog and the Books Online both cover creating the Windows Logins as SQL Logins to avoid running under escalated privileges but no one seems to offer this advice or follow it.  Something new to consider and pass along on the forums as I answer questions.