Resetting the SQL Server Express Password

This is mainly a note for myself, since I already had to google for this several times.

I am using the VS-2010 VM image from Microsoft to experiment with TFS 2010. This image has a SQLEXPRESS instance installed. The given instance has an ‘sa’ user with admin rights. The local machine administrator user ‘Administrator’ however has not admin rights on the instance.

Unfortunately I could not find the password of the ‘sa’ user of the SQLEXPRESS instance.

I am playing with the SourceSafe to TFS converter tool (vssconverter). To actually run the source-control migration, the tool wants to create a database on the ./SQLEXPRESS instance. I am running the tool with the local ‘Administrator’ account, which does not have this right … bummer!

The solution: Start the SQLEXPRESS instance in single-user mode. In the single-user mode local admin accounts have admin rights on the instance.

• Start SQL Server Configuration Manager
• Select the instance you want to start in single-user mode
• Right click, select ‘Properties’, chose ‘Advanced’ tab
• Prepend ‘-m;’ in ‘Startup Parameters’

• Restart the instance
• Connect to the instance with SQL Server Management Studio using the local Admin account
• Under Security->Logins double-click ‘BUILTIN\Users’ and add the aproriate server roles (i.e dbcreator)
• (optional) double-click ‘sa’ login and set a new password
• In SQL Server Configuration Manager, remove the ‘-m;’ from ‘Startup Parametrs’ and restart the instance

Now you can connect with ‘sa’ and the new password.
And you can run vssconverter with the ‘Administrator’ account, and it is able to create a database.

This solution should also work for regular SQL Server instances, not only for Express instances, however I did not test that.