It happens more often than you’d think. A server is joined to a domain, or taken off one, or the DBA who set it up leaves, and suddenly nobody can get into SQL Server as sysadmin.

You don’t need a password to fix it. Anyone in the server’s local Administrators group can get back in by starting SQL Server in single-user mode for a few minutes. This isn’t a security hole: you need administrator rights on the Windows server itself.

This works the same on SQL Server 2016, 2017, 2019 and 2022, Standard or Enterprise.

Before you start

• This takes SQL Server offline. Plan a few minutes of downtime.
• Find the instance’s service name. A default instance is MSSQLSERVER (its Agent is SQLSERVERAGENT). A named instance called PROD is MSSQL$PROD (its Agent is SQLAgent$PROD). To list what’s on the server:

sc query type= service state= all | find "MSSQL"

• Decide which account to make sysadmin: normally your domain account, e.g. DOMAIN\YourName, or, if the server is no longer on the domain, a local one, e.g. SERVERNAME\Administrator.

1. Open a Command Prompt as administrator
Right-click, Run as administrator. This matters: without it, Windows doesn’t pass on your Administrators membership and SQL Server won’t let you in.

2. Stop SQL Server Agent, then SQL Server

net stop SQLSERVERAGENT
net stop MSSQLSERVER

3. Start SQL Server in single-user mode, letting only sqlcmd connect

net start MSSQLSERVER /mSQLCMD

Single-user mode allows just one connection. The SQLCMD part stops anything else from taking it first, such as SQL Agent, the telemetry service, monitoring tools or SSMS. Type SQLCMD exactly as shown, in capitals.

4. Connect with sqlcmd using your Windows login

sqlcmd -S . -E -C

For a named instance use -S .\PROD. The -C switch trusts the server’s own certificate: the newer sqlcmd that comes with SQL Server 2022 won’t connect without it, and older versions simply ignore it.

5. Add your account as sysadmin
Replace the account name with yours:

IF SUSER_ID(N'DOMAIN\YourName') IS NULL CREATE LOGIN [DOMAIN\YourName] FROM WINDOWS;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\YourName];
GO
exit

6. Restart SQL Server normally, then SQL Agent

net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

7. Connect in SSMS with your Windows account
You’re sysadmin again. Add any other DBAs now, while you’re in.

If it doesn’t work

• “Login failed” in step 4: either the Command Prompt isn’t running as administrator, or your account isn’t in the server’s local Administrators group.
• “Server is in single user mode. Only one administrator can connect at this time.” Something else got in first. Stop SQL Server again and repeat step 3, making sure /mSQLCMD is typed exactly.
• “The certificate chain was issued by an authority that is not trusted”: add -C to the sqlcmd command.
• Failover cluster instances: don’t use net start here, or the cluster will fight you. Take the SQL Server role offline in Failover Cluster Manager, and start the instance from the command line on the owning node.

The one-line alternative
If the server has PowerShell and the free dbatools module, this does all of the above for you. Run it from PowerShell as administrator:

Reset-DbaAdmin -SqlInstance . -Login 'DOMAIN\YourName'

Why not the old “enable sa” method?
Enabling the sa account and setting its password only works if the server allows SQL Server logins (mixed mode), and it leaves a powerful account switched on. Adding your own Windows account works everywhere and changes nothing else.