Deploying our development machines from virtual machine templates saves us a lot of time, however every now and then we hit frustrating little speed bumps. Here is the latest – SQL Server 2008 / SQL Server 2008 Express not having an administrator account enabled.

To fix this problem you must first stop the SQL Server 2008 instance, and start it in “single user mode” (you need to be local administrator for this to work).

1. Stop the SQL Server instance in Service Manager
2. Open cmd prompt
3. browse to appropriate SQL Server installation: e.g. cd\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn
4. Start SQL Server in single user mode using the “-m” switch:
   For a default instance: sqlservr.exe -m
   For SQLEXPRESS/named instance: sqlservr.exe -m -sSQLEXPRESS
   
5. You can now connect to the SQL Server instance via SQL Server Management Console and create your administrative user, or use SQLCMD from the command prompt e.g.:

   C:\> SQLCMD -S .\SQLEXPRESS -E
   1> CREATE LOGIN [YOURDOMAIN\justin] FROM WINDOWS
   2> go
   1> exec sp_addsrvrolemember @loginame='YOURDOMAIN\justin', @rolename='sysadmin'
   2> go
   

6. Stop the server (Ctrl+C)
7. Start the service again from Service Manager