Adding Logins to SQL Server 2008 (User Provisioning)

Today I learnt an important lesson about moving a PC from one Active Directory domain to another – the process will strip (domain) logins from SQL Server!  Yes, I know this is kind of obvious and expected, but also easy to overlook.

So our scenario is that we don’t have any Windows Accounts which can authenticate to SQL Server (via SQL Management Studio or via command line tools) and/or we have no accounts as members of the sysadmin role.  the “sa” account is not usable because mixed mode authentication is not enabled.  We can’t administer the SQL instance.

This is a big problem if you rely on Windows Authentication (i.e. no mixed mode authentication), and you haven’t got any local (built in or otherwise) accounts which have been assigned sysadmin permissions. 

You’re basically stuck!  You can’t log onto the instance and you can’t administer SQL Server, even locally.  You can’t use the Dedicated Administration Console (DAC) either since it requires the active user to be in the sysadmin role!

SQL Server 2005 Service Pack 2 came with a handy utility called the User Provisioning Tool for Windows Vista (sqlprov.exe) and allowed you to assign sysadmin logins for your account (and other accounts).  This tool unsurprisingly doesn’t work with SQL Server 2008, and as far as I can tell there is no equivalent tool available (please correct me if I am wrong).

At this point you might be reaching for the installation media for a reinstall, but never fear – there is a solution.  The solution was actually quite obvious now that I think about it. You have to start SQL Server in single user mode.

This allows you to log on (using SSMS) from the localhost with sysadmin permissions. From there, you can then create new logins (including domain account logins, if so desired).

Here’s how to start SQL Server in single user mode and here’s more information on how to start SQL Server from the command prompt (which you will have to do for single user mode).

Big thanks to Rob Farley for supplying the suggestions.