Locked Out of SQL Server: Regain Sysadmin When Nobody Has It

Everybody who had sysadmin has left, sa is disabled or its password went with them, and the instance is still serving its application perfectly well. Nothing is down, which is why this sits in a ticket for weeks until the day somebody needs to add a login and finds they cannot.

The recovery is built into the engine and it is not a crack. It works because somebody who can stop and start the SQL Server service already owns the machine, and therefore already owns the data. That is also why it leaves a trail you should expect to be asked about.

First, Check Whether You Are Actually Locked Out

Do this before you plan an outage. A surprising number of “nobody has sysadmin” instances still carry a Windows group that somebody is a member of. Run this from any login that can read sys.server_role_members, which is most of them:

SELECT m.name AS member_name, m.type_desc, m.is_disabled
FROM sys.server_role_members rm
JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals m ON m.principal_id = rm.member_principal_id
WHERE r.name = 'sysadmin'
ORDER BY m.name;

Three things in that result decide your next hour. A WINDOWS_GROUP row means ask who is in the group, because that is a five-minute fix rather than an outage. An is_disabled of 1 means the login exists and somebody turned it off, which is a different job from a lost password. And a login nobody recognises is worth a note in the ticket before you change anything.

Here is the before and after from the reproduction behind this post. A SQL login called DbaLead was the working sysadmin, then it was dropped from the role and sa was disabled, which is the state an instance is usually found in:

member_name                     type_desc       is_disabled
------------------------------  --------------  -----------
BUILTIN\Administrators          WINDOWS_GROUP             0
DbaLead                         SQL_LOGIN                 0
NT AUTHORITY\NETWORK SERVICE    WINDOWS_LOGIN             0
sa                              SQL_LOGIN                 0

-- ALTER SERVER ROLE sysadmin DROP MEMBER DbaLead;
-- ALTER LOGIN sa DISABLE;

member_name                     type_desc       is_disabled
------------------------------  --------------  -----------
BUILTIN\Administrators          WINDOWS_GROUP             0
NT AUTHORITY\NETWORK SERVICE    WINDOWS_LOGIN             0
sa                              SQL_LOGIN                 1

On a hardened Windows instance BUILTIN\Administrators is usually gone, removed on purpose years ago, which is exactly how an instance ends up with no reachable sysadmin at all. Get Sysadmin Members is the same check written up with the output you should be keeping on file.

What the Lockout Looks Like From the Inside

There are two separate messages here and people quote the wrong one half the time. If sa is disabled, the connection is refused before you run anything at all:

If instead you are a login that still connects but no longer holds the role, everything looks normal until you try to use it. This is the pair of refusals captured from the reproduction, one attempt to put the login back in the role and one attempt to re-enable sa:

is_sysadmin whoami
----------- -------
          0 DbaLead

Msg 15151, Level 16, State 1, Line 1
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'sa', because it does not exist or you do not have permission.

Note what 15151 says and what it refuses to say. It will not tell you whether the object is missing or whether you lack permission, because telling you would confirm the existence of a login to somebody who should not know. That is deliberate, and it is why the message is useless as a diagnostic on its own. SELECT IS_SRVROLEMEMBER('sysadmin') returning 0 is the answer you actually want.

If the refusal you are holding is a plain 18456 with a state number instead, you have a different problem, and Login Failed for User (Error 18456) is the page that decodes it.

The Recovery on Windows: Single-User Mode

When the engine is started with the -m startup option it allows exactly one connection, and it grants sysadmin to any member of the computer’s local Administrators group even though that group is not in the role. That is the whole mechanism. MS Docs sets it out in Connect to SQL Server when system administrators are locked out.

You need an outage window. The instance serves one connection and nothing else while you do this, so agree the window first and tell the application owners the length, not the reason.

1. Stop SQL Server Agent, then the engine. Agent is the usual thief of the one connection, so stop it first and leave it stopped.

# Run from an elevated prompt on the server itself.
Stop-Service SQLSERVERAGENT -Force
Stop-Service MSSQLSERVER -Force

2. Start it with the single-user option. There are two documented ways and they are not equivalent. The net start form is temporary, which is what you want, because the parameter is gone the moment the service stops again:

# Default instance. The quotes matter, and so does the lack of a space after /m.
net start MSSQLSERVER /m"SQLCMD"

# A named instance takes the service name, not the instance name.
net start MSSQL$SQL2022 /m"SQLCMD"

The other way is SQL Server Configuration Manager: Properties on the service, the Startup Parameters tab, add -m, start the service. That one is persistent. It stays in the registry until you go back and remove it, and an instance still in single-user mode after a reboot is its own incident. Use it only if the service will not start any other way, and take it out in the same session. MS Docs covers both in Start SQL Server in single-user mode, with the full option list in Database Engine service startup options.

3. Connect and put a group back in the role. Use sqlcmd, not SSMS. Object Explorer opens more than one connection, takes the slot, and then fails:

-- sqlcmd -S . -E -C
CREATE LOGIN [CONTOSO\SqlAdmins] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\SqlAdmins];
GO

Add a group, not a person. A group is the thing you can change next time without another outage, and it is the difference between fixing the instance and recreating the same problem with your own name on it. If you must re-enable sa, do it with a new password and expect to justify it:

ALTER LOGIN sa WITH PASSWORD = N'a new one, from the password manager';
ALTER LOGIN sa ENABLE;
GO

4. Restart normally and prove it. Stop the service, start it with no parameters, start Agent, then run the sysadmin query from the top of this page again and keep the output. This is that confirmation from the reproduction, with the role membership restored:

name    is_disabled is_sysadmin
------- ----------- -----------
sa                0           1
DbaLead           0           0

-- ALTER SERVER ROLE sysadmin ADD MEMBER DbaLead;

member_name                     type_desc       is_disabled
------------------------------  --------------  -----------
BUILTIN\Administrators          WINDOWS_GROUP             0
DbaLead                         SQL_LOGIN                 0
NT AUTHORITY\NETWORK SERVICE    WINDOWS_LOGIN             0
sa                              SQL_LOGIN                 0

The Trap: Something Else Takes the One Connection

This is the step that costs people the outage window. Single-user mode means one connection for the whole instance, and SQL Server Agent, a monitoring agent, a backup tool or an application connection pool will reach it before you do. When it happens you get this, and the engine writes it to the error log every single time:

From the error log of the reproduction, with one connection deliberately held open and a second attempted every few seconds:

07:58:17.46 Logon       Error: 18461, Severity: 14, State: 1.
07:58:17.46 Logon       Login failed for user 'DbaLead'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: 172.17.0.2]
07:58:20.47 Logon       Error: 18461, Severity: 14, State: 1.
07:58:20.47 Logon       Login failed for user 'DbaLead'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: 172.17.0.2]

The fix is in the startup option itself.

Why It Is -m"SQLCMD" and Not Just -m

-m"SQLCMD" restricts the single connection to clients whose application name is SQLCMD, which is what the sqlcmd utility reports. Everything else is refused even when the slot is free. That is worth seeing proved, because it is the difference between one attempt and six. Here is sqlcmd connected to an instance started with -m"SQLCMD" reporting its own program name, then bcp refused on the same instance with nothing else connected:

program_name
------------
SQLCMD

(1 rows affected)

SQLState = 37000, NativeError = 18461
Error = [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time.

Two warnings about that option. It is a string match on the application name and nothing more, so it is a way to keep Agent out, not a security control. And if you start with -m"SQLCMD" you have to connect with sqlcmd, because SSMS is now refused for the same reason everything else is. sqlcmd Examples for SQL Server has the connection strings if you do not use it often.

On Linux and in Containers It Is a Different Command

The Windows recovery leans on the local Administrators group, and Linux does not have one, so -m on its own gets you a single connection you still cannot authenticate. Microsoft ships a separate tool for it. Stop the engine, run mssql-conf set-sa-password as root, start it again:

sudo systemctl stop mssql-server
sudo MSSQL_SA_PASSWORD='a new one, from the password manager' /opt/mssql/bin/mssql-conf set-sa-password
sudo systemctl start mssql-server

The output of that run, and the detail nobody mentions: it re-enables the account as well as changing the password. A disabled sa comes back enabled, which is the whole recovery in one command:

Configuring SQL Server...
The system administrator password has been changed.
Please run 'sudo systemctl start mssql-server' to start SQL Server.

Once you are in as sa, the T-SQL is identical to the Windows path: ALTER SERVER ROLE sysadmin ADD MEMBER, then set a fresh sa password and disable the account again if that was your standard. The tool is documented in Configure SQL Server on Linux with the mssql-conf tool. In a container there is no service manager, so you stop the sqlservr process, run the same mssql-conf command as root, and start /opt/mssql/bin/sqlservr again as the mssql user. The same binary takes -m"SQLCMD" directly if you want single-user mode there.

Instance Single-User Mode Is Not Database SINGLE_USER

These two get conflated in nearly every forum thread on the subject, and the commands have nothing in common.

• Instance single-user mode is the -m startup option. It is set outside SQL Server, it applies to the whole engine, it lasts only as long as the current service start if you set it with net start, and it is what this page is about.
• Database SINGLE_USER is ALTER DATABASE ... SET SINGLE_USER. It is set from inside SQL Server, it applies to one database, it is persisted in that database’s metadata, and it is how a restore or a repair gets exclusive use. Locking yourself out of a database that way is a real and separate incident, covered in The Database Is in Single User Mode.

The practical consequence: starting the instance with -m does nothing at all for a database stuck in SINGLE_USER, and taking a database out of SINGLE_USER does nothing for a lost sysadmin. If you are here because of error 5030 or a database you cannot get exclusive access to, you are on the wrong page.

Do It With an Audit Trail, Not Quietly

Everything above is a documented administrative procedure, and it is also indistinguishable from somebody taking over an instance they should not have. The difference is entirely in what you write down, so write it down before you start rather than after.

• Get it approved in writing by whoever owns the data, not by whoever owns the server. A change record with a time window and a named approver is the thing that makes this routine instead of memorable.
• Capture the sysadmin membership before and after. The query at the top of this page, run twice, saved to the ticket. If anyone asks later what you granted, that is the answer and it takes ten seconds.
• Keep the error log. The restart, the single-user start and every 18461 are in it with timestamps, and it is the part of the record you did not write. Copy the file off before it rolls.
• Grant a group, then stop. Add one Windows group and leave the rest of the security model alone. The temptation when you have the instance to yourself is to tidy up, and a change record that says “regained sysadmin” next to a diff showing nine other things is how this becomes an audit finding.
• Close the hole that caused it. One named human holding sysadmin is how an instance gets here. Get Login Security Audit lists the logins, their roles and their last use, and it is the report to run the week after, not the week it happens again.

On who needs what: the recovery needs a local administrator on the Windows server, or root on a Linux host, because both routes require stopping and starting the service. It needs no SQL Server permission at all. That is the honest security statement about this procedure, and it is also why the list of people with local admin on a database server should be shorter than the list of people with sysadmin. If you cannot stop the service, you cannot do this, and the correct next step is to find the person who can. MS Docs describes the roles involved in Server-level roles and the statement itself in ALTER SERVER ROLE.

What Not to Do

Do not download a password recovery tool. The search results for this problem are full of them. They work by doing something cruder than the procedure above, usually by editing the master database file directly, and they ask you to hand an unknown binary the keys to a production instance. The engine has a supported route and it is four commands.

Do not leave -m in the startup parameters. Set through Configuration Manager it persists across restarts, so the next unplanned reboot brings the instance up accepting exactly one connection. The application fails with 18461 and nobody connects the two events.

Do not leave sa enabled with its password in the ticket. If you enabled it to recover, disable it again once a group holds sysadmin, and rotate the password you typed. A password that has been in a change record is a shared password.

Do not use SSMS for the single-user connection. Object Explorer opens a second connection for its own queries, takes the one slot, and then reports that it cannot connect. Use sqlcmd, as in step 3, and expect the whole step to feel awkward.

Common Questions