When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password

  • Person who installed the SQL Server knows the password but has left the building.
  • You did not write down the password in your password file
  • Password file is lost
  • …

Steps to recover the SA password

  • Start SQL Server Configuration Manager

  • Stop the SQL services

  • Edit the properties of the SQL Service

  • Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

  • Start the SQL services. These are now running in Single User Mode.
  • Start CMD on tthe SQL server
  • Start the SQLCMD command. Now you will see following screen

  • Now we create a new user. Enter following commands
    • CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy
    • Go
      • Now this user is created
  • Now we grant the user a SYSADMIN roles using the same SQLCMD window.
    • sp_addsrvrolemember ‘recovery’, ‘sysadmin’
    • go
  • Stop the SQL service again
  • Change the SQL service properties back to the default settings

  • Start the SQL service again and use the new created login (recovery in my example)
  • Go via the security panel to the properties and change the password of the SA account.

  • Now write down the new SA password.