How to Recover SA Password on Microsoft SQL Server

October 24th, 2013 by Admin Leave a reply »

If you ever lost SA password, you may have thought your only option is to reinstall SQL Server and re-attach all of the user databases. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master database. Simply start SQL Server in single user mode and you can recover SA password easily with the OSQL command. Here are the detailed steps:

Part 1: Start SQL Server in Single User Mode

  1. Open SQL Server Configuration Manager.
  2. Find the SQL Server instance you need to recover the SA password.
  3. Stop the SQL Server instance.
  4. Right-click on the instance and select Properties.
  5. Click on the Advanced tab, and add -m; to the beginning of Startup parameter.
  6. Click OK and start the instance.

Part 2: Recover SQL Server SA Password

  1. Open an elevated command prompt and enter the command:
    osql -S myServer\instanceName -E
    Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
  2. At the next prompts, enter the following commands:
    1> alter login sa enable
    2> go
    1> sp_password NULL,'new_password','sa'
    2> go
    1> quit
  3. Stop the SQL Server instance.
  4. Remove the -m option from the Start parameters field, and then start the SQL Server service.

At this point you should be able to login to SQL Server using the SA user account and the new password you gave it. Besides recovering SA password with OSQL, you can also reset / change your forgotten SA password using the third-party software such as SQL Server Password Changer.