Reset SQL Server sa Password

Recently I had a request to reset SQl server sa password. The sa was set up long time ago, but it was not documented. SQL as log in was required to set up administration configurations. This kind of case is very common at work. The following are my notes about this experience.

I soon found a solution to resolve the issue. Basically, you need to restart SQL server in single mode first. Then log in to the server machine by using user with Administrative privilege. The steps are very straight forward, however, I was caught for a while with one mistake. You have to put "-m;" in start up parameter at the beginning with no space afterwards.

The following is the screen snapshot of the property page within SQL server configuration tool. By adding "-m;" right at the beginning line of Startup Parameters, the SQL server will be in single mode in the next start up.



Although I was able to reset sa password by using SQL management studio after  SQL server was restarted, I could not log in to SQL server by sa afterwards. It was interesting that the sa Log was set to blocked. I had to unblock it with single mode on second try.  The following was my snapshot of correct setting. Note: the check box of "Login is blocked out" was set when I reset sa password first time.

Reference

• Change Password of SA Login