Once upon a time a vendor set up a new SQL Server at the company I was working for. For whatever reason they decided it best to not work with the local DBA. A few weeks later a server admin emails me asking if I can help out with an issue they were having. He says the vendor has lost the sa password and cannot connect to the SQL Server instance. After having a good chuckle I agreed to help out and reset the SQL Server sa password. Here is how I did it:

First things first. You need to be logged in to the server with an account that is a member of the local administrators group on the server. Without that all that follows will not work for you. You also need to be able to stop the SQL Server service. This means the database(s) will be offline for a period of time. Do not do this on a production server unless you have scheduled down time.

With that said the first thing we need to do is stop the SQL Server service. You can do this several different ways. The easiest method is using the SQL Server Configuration Manager. Open it up, right click the SQL Server service, and click Stop.

Now that the service has stopped we need to add the -m startup parameter to the service. This will force SQL Server to start in single-user mode the next time it starts. We want it in single-user mode because this grants anyone in the local administrators group on the server sysadmin privileges. To add the new startup parameter right click on the SQL Server service and click properties. Then click the Startup Parameters tab, type -m in the Specify a startup parameter text box, click add, and then OK.

Once you click Apply a pop up box will come up telling you that you have to restart the service for the change to take effect. Click OK to close the pop up. Now right click the SQL Server service and click start.

Now that our SQL Server instance is up and running in single-user mode, we need to connect to it and modify permissions. To connect to the instance start an elevated command prompt. Once in command prompt type sqlcmd and hit enter.

Finally we are at the point where we can reset the sa password. In this example I chose to reset the password to 12345. I suggest you reset yours to something much more complex. Simply type in ALTER LOGIN sa WITH PASSWORD = ‘12345’, hit enter, type in GO, and hit enter one last time.

With the sa password reset you can now login to the SQL instance and get what you need done. Make sure to remove the single-user mode parameter and restart the instance. Or that could be a fun problem for one of your coworkers to try and figure out…