sodcalculator

10 Tech How-To

Recover Access to SQL Server Instance

Guide 10-52745 · Updated · Step by step

Losing access to a SQL Server instance can halt development, testing, or production work. This guide walks through the standard recovery methods, from using built-in Windows administrator privileges to resetting the sa password, and covers what to do when you’re locked out entirely.

What You’ll Need

  • Windows administrator account on the server (or domain admin rights, if the server is domain-joined).
  • SQL Server installation media or access to the original setup files (for the repair option).
  • SQL Server Management Studio (SSMS) or another query tool, if you plan to run T-SQL commands after regaining entry.
  • Knowledge of the SQL Server version and edition (e.g., SQL Server 2019 Standard).

Step-by-Step

  1. Stop the SQL Server service. Open Services (Win+R, type services.msc). Find the service named SQL Server (MSSQLSERVER) for a default instance, or SQL Server (InstanceName) for a named instance. Right-click and select Stop.
  2. Start SQL Server in single-user mode. Open a Command Prompt as Administrator. Navigate to the SQL Server Binn folder (typically C:\Program Files\Microsoft SQL Server\MSSQL{version}.{instance}\MSSQL\Binn). Run: sqlservr.exe -m"SQLCMD". The -m flag starts the server in single-user mode, and "SQLCMD" limits connections to the SQLCMD utility. Keep this window open.
  3. Open a second Command Prompt and run SQLCMD to connect. Type: sqlcmd -S . -E (for Windows authentication). If you’re on the same machine, . represents the local default instance. For a named instance, use .\InstanceName.
  4. Create a new sysadmin login. At the SQLCMD prompt, run the following T-SQL to add a new login and make it a sysadmin. Replace NewAdmin and YourStrongPassword! with your own values:
    CREATE LOGIN [NewAdmin] WITH PASSWORD = 'YourStrongPassword!';
    GO
    ALTER SERVER ROLE sysadmin ADD MEMBER [NewAdmin];
    GO

    Press Enter after each line, then type GO on a new line to execute.

  5. Shut down the single-user mode server. In the first Command Prompt (where sqlservr.exe is running), press Ctrl+C. Wait for the process to stop completely.
  6. Restart SQL Server normally. Go back to Services, right-click the SQL Server service, and select Start.
  7. Connect with your new login. Open SSMS, choose SQL Server Authentication, enter NewAdmin and the password you set. You now have sysadmin access.
  8. Optional: Reset the sa password. With your new admin login, run:
    ALTER LOGIN sa WITH PASSWORD = 'NewStrongPassword';
    GO

    Then enable the sa login if it was disabled: ALTER LOGIN sa ENABLE;

Common Mistakes

  • Forgetting to stop the service before running sqlservr.exe. If the service is still running, the single-user instance will fail to start because the port is already in use.
  • Using -m without the SQLCMD qualifier. Without "SQLCMD", any client can connect, which can be a security risk. Always use the qualifier.
  • Not running the Command Prompt as Administrator. SQL Server files and registry keys require elevated permissions. Without admin rights, the commands will fail.
  • Leaving single-user mode running. If you forget to stop the sqlservr.exe process, the server remains in single-user mode, and no other connections can be made. Always shut it down after making changes.
  • Typing the T-SQL incorrectly in SQLCMD. SQLCMD requires GO on its own line to execute batches. Missing it will leave the command pending.

Tips

  • Before starting this process, take a full backup of the master database if possible (you might not be able to if you’re locked out, but do it after regaining access).
  • Use a strong password for the new admin login and store it in a secure password manager.
  • If you have another sysadmin account that works, you can simply use that to reset the lost password—no need for single-user mode.
  • Consider enabling mixed mode authentication only if you actually need SQL logins. Windows authentication is generally more secure.
  • For SQL Server on Linux, the process is similar but uses mssql-conf to set the MSSQL_SA_PASSWORD environment variable, and you can use sqlcmd with -S localhost.

Troubleshooting

  • Error: “The service did not respond to the start or control request in a timely fashion.” This often means the sqlservr.exe process is still running from single-user mode. Open Task Manager, find sqlservr.exe, end it, then restart the service.
  • SQLCMD cannot connect. Check that you used the correct instance name. For a default instance, -S . works. For a named instance, use -S .\InstanceName. Also ensure the single-user server is still running in the first window.
  • Login failed for user ‘NewAdmin’. The login might not have been created because the T-SQL batch didn’t execute. Re-run the steps, making sure to type GO after the CREATE LOGIN and ALTER ROLE statements.
  • You don’t have Windows administrator rights. If you’re not a local admin, you cannot start the service in single-user mode. Contact your system administrator or domain admin for help.
  • SQL Server won’t start after the process. If you accidentally corrupted the master database, you may need to restore it from backup or run a repair installation. Use the original installation media and select Repair.
  • Port conflict. If another instance is using the same port, the single-user server might fail to start. Check the error log in the first Command Prompt window for details.

Frequently Asked Questions

Can I reset the sa password without stopping the service?

No, you must stop the service to start in single-user mode. However, if you already have another sysadmin login, you can reset the sa password without stopping the service.

What if I don’t have Windows admin rights on the server?

You cannot use this method. You need to ask a Windows administrator to perform the steps or grant you temporary admin rights.

Will this affect my databases?

No, this process only changes server-level security. Your user databases remain untouched. However, always back up the master database after making changes.

Can I use this method on SQL Server Express?

Yes, the same steps work for Express editions, but the service name will be SQL Server (SQLEXPRESS) and the Binn folder path will include SQLEXPRESS.

What if I’m locked out of a SQL Server on a different machine?

You need to have local admin rights on that machine. If you don’t, you cannot use this method remotely. You’ll need to contact the server’s administrator.

Is there a way to recover the original password instead of resetting it?

No, SQL Server passwords are stored as hashes and cannot be reversed. The only option is to reset it, which requires sysadmin access or the single-user mode method described above.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →