Recovering a SQLEXPRESS Installation

I inherited a desktop machine at work. It wasn’t scrubbed and re-fitted with a fresh install when I arrived. Recently I have been examining processes for Single-Sign-On, and really needed to play around with the local installation of SqlExpress, so as not to screw up the dev database for everyone else. One problem though, the previous tenant of the machine followed best security practices and remove the BUILTIN\Administrators user from the database and I nobody knew the System Administrator (sa) password - add to that, the sa account was disabled (again, good security practice!).

With that in mind, I set out on the path to discover how I can recover that installation, and not have to go through the process of uninstalling and re-installing SqlExpress. I finally came across this old post at Phoenix blog.

A few things to keep in mind; His instructions are for full-blown SQL Server, so instead of using “NET STOP MSSQLSERVER” you’ll have to use “NET STOP MSSQL$SQLEXPRESS”. This is for all the stop and starts. Also, one has to have local administrator privileges on the machine, if you don’t have that, as far as I know, you’re pretty much up a creek without a paddle. The process is quick and simple really, I’ll post the steps here (without the nice screen-scrapes that Deepak has in his blog, you can go there and look at them if you need to).
1) Open command line window
2) type NET STOP MSSQL$SQLEXPRESS and wait for the service to stop
3) type NET START MSSQL$SQLEXPRESS /m - this puts sql server in single-user mode (I am not sure what that is really, but hey,it works!)
4) Open your SQL Server Management Studio, and click “New Query” you’ll be presented with the connection dialog window, select the instance of SqlExpress and open the connection.
5) In the new query window type “sp_addsrvrolemember ‘YOUR_USER_NAME’, ‘SYSADMIN’” (sans quotes, of course). This will add your login to the sysadmin role.
6) Close Management Studio.
7) Back in the command prompt window type “NET STOP MSSQL$SQLEXPRESS” to stop the service
8) Next time “NET START MSSQL$SQLEXPRESS” in the command prompt to start the service again, but in normal mode (not single user mode this time, notice the lack of ‘/m’ switch in the command.