Reset sa password in SQL server

By putting SQL Server into single-user mode, you can log in using any Windows administrator account with sysadmin permissions, even if an equivalent SQL login does not exist. This is very useful when you’ve lost sysadmin access (not-entirely-hypothetical example: someone set up a database, removed all sysadmin accounts except sa, and then couldn’t remember the sa password a few weeks down the line), since you can reset the sa password (or the passwords for any other account, for that matter). This post has more details, but essentially:

  1. Open the SQL Server Configuration Manager
  2. Click on the SQL Server 200{5,8} Services leaf, and stop the SQL Server instance you want to put into single-user mode
  3. Open the properties of the instance, go to the Advanced tab, and in the Startup Parameters option, add “;–m” (exactly, without quotes) to the end of the existing value
  4. Click OK, and restart the SQL Server instance
  5. You can now use sqlcmd with a Windows administrator login to execute SQL commands against the instance
  6. Once you’ve finished, don’t forget to remove the -m parameter and restart the SQL instance again to leave single-user mode 😉

Something to add?

This site uses Akismet to reduce spam. Learn how your comment data is processed.