Recovering the sa Password on a Local SQL Server

Forgetting the sa password on your local SQL Server can be stressful, but don’t worry—there’s a way to recover it. Below, I’ll walk you through the steps to reset the sa password using SQL Server in single-user mode.

Steps to Recover the sa Password

1. Stop the SQL Server Service

   First, you’ll need to stop the SQL Server service to start it in single-user mode:

   net stop MSSQLSERVER
   

2. Start SQL Server in Single-User Mode

Now, start SQL Server in single-user mode. This mode allows you to connect with SQLCMD and reset the sa password:

net start MSSQLSERVER /m

1. Connect to SQL Server Using SQLCMD

Open a command prompt and connect to SQL Server using SQLCMD:

SQLCMD -Slocalhost -E

1. Enable the sa Login

Once connected, enable the sa login:

ALTER LOGIN sa ENABLE;
GO

1. Reset the sa Password

Now, reset the sa password to something strong and secure:

ALTER LOGIN sa WITH PASSWORD = 'NewSuper1StrongPassword!';
GO

1. Create a New Login (Optional)

If you need an additional way to connect as a system administrator, you can create a new login using your Windows username:

CREATE LOGIN [YOUR-PC-NAME\windows_username] FROM WINDOWS;
GO

Then, add the new login to the sysadmin role:

ALTER SERVER ROLE sysadmin ADD MEMBER [YOUR-PC-NAME\windows_username];
GO

1. Restart SQL Server in Normal Mode

Finally, stop SQL Server and restart it normally:

net stop MSSQLSERVER
net start MSSQLSERVER

And that’s it! You’ve successfully recovered the sa password on your local SQL Server. Remember to keep this password safe and secure, and consider creating an additional login with sysadmin privileges as a backup.