How to Recover the SA Password (Part 2)


Even though I have already published the article about the sa password recovery, this Part 2 adds some interesting stuff. This time everything is done via command line interface

1. Stop SQL server service

 

  • net stop MSSQL$INSTANCE – to stop SQL server with non-default instance, for example, use MSSQL$EXPRESS for SQL Express.
  • net stop MSSQLSERVER – to stop SQL server with default instance

2. Start SQL server service in single user mode

  • net start MSSQLSERVER /m”SQLCMD” – to start SQL server in single user mode

Note, that SQLCMD should be in upper-case letters

3. Run sqlcmd

  • sqlcmd -E -S SERVER_NAME\INSTANCE – for server with non-default instance
  • sqlcmd -E -S SERVER_NAME or sqlcmd -E -S . – for server with default instance

4. Reset the password

This action can be done with two different ways. Brave men can change the sa password directly, the more careful people can first create a new sysadmin account and use it to log into SSMS and change the sa password via GUI interface

  • Create a new sysadmin account

1> create login new_sa with password = ‘New_Password1’
2> sp_addsrvrolemember new_sa, sysadmin
3> go
1> exit

  • Change the sa password directly

1> alter login sa enable
2> sp_password NULL, ‘New_Password1′,’sa’
3> go
1> exit

5. Restart SQL server in normal mode

net stop MSSQLSERVER
net start MSSQLSERVER

It will do the trick

 

Advertisements

Comments are closed.

%d bloggers like this: