Reset SQL SA Password

For SQL Server Password Recovery – Reset SQL SA user if password is lost.

So you can’t login to your SQL Server, or you have lost your SA password. Don’t worry, all is not lost, there is a way to recover. Log onto the server hosting the SQL as an admin then follow along:

Here is the quick strait forward answer you are looking for, optionally there is a deep dive below as well.

Run PowerShell as Administrator
Run these commands: (it’s ok to leave the comments)

net stop MSSQLSERVER #This stops the SQL Server service
net start MSSQLSERVER /m"SQLCMD" #This starts the SQL Server in Single User Mode and with SQLCMD enabled to run SQL at the command line
SQLCMD #This opens a SQL CMD prompt
ALTER LOGIN sa WITH PASSWORD=N'SetYourStrongPassword!!11!'
GO
exit
net stop MSSQLSERVER #This stops the SQL Server service
net start MSSQLSERVER #This starts the SQL Server service in a normal state

If you just needed a quick reset, then that is it, you are welcome.

A Deep Dive

So you want more details, or maybe something is different about your setup and cause an error. Let’s walk through each step and get you more details on what’s happening and what we can do to get you back in your SQL Server.

Make sure to Run PowerShell as Administrator

Tip: Make it easy on yourself, open PowerShell, right click it on the Taskbar, Pin to Taskbar it. Then right click PowerShell on the Taskbar and chose Run as Administrator

Note, when stopping the MS SQL Server with net stop MSSQLSERVER you may get message about a dependency services needing to stop too. This is normal, for example you may have a service running to allow SQL jobs to be automated, this of course relies on the SQL Server to be running to run the jobs. You will need to stop those dependency services too, the command line will prompt you a yes/no if this is the case for you.

If you are getting error about stopping the service in general, more details should be logged to the Windows Event Log.

If you are getting an error about starting the service, make sure the service is stopped. You can’t start a service that is already running, also more details about a start error should be logged to the Windows Event Log.

Note, I’ve seen a lot of other how-to use a -m”SQLCMD” vs using a /m”SQLCMD”, (highlighting the -m vs /m), but I have not had any luck with -m as the SQLCMD command then tries to start as the domain user you are logged in as and gives  access errors.

At this point you can basically run any normal TSQL, as you would in the Microsoft SQL Server Management Studio (SSMS). In this example we are resetting the password the default SA (system admin) account. You could create a new user or add a domain user or group if the SQL server set to Windows Authentication only. Just don’t forget to give them sysadmin permission.

It is important to run a GO after entering all your TSQL, or else the commands won’t actually run.

Exit the SQLCMD

Just another stop and start to restart the SQL Service, alternately you could restart the server at this point to get a clean start (Hey, you could even do it the PowerShell way with the command: Restart-Computer)

Putting it all together, here is an example of a successful reset.

PowerShell, SQL, SQLCMD, Sysadmin