Sev1: nobody has sysadmin on SQL Server. Regaining access with single-user mode

#Symptom

• Every DBA gets Login failed or can connect but has no rights to fix anything.
• sa is disabled or its password is unknown.
• Typical causes: a domain group used for sysadmin was deleted or renamed, an account clean-up removed the last admin login, or the instance was built by someone who left.

#How this works

When SQL Server is started in single-user mode (-m), members of the local Administrators group on the server can connect as sysadmin. You use that one connection to create or fix an admin login, then restart normally.

You need: local administrator rights on the server, and a maintenance window. Starting in single-user mode stops the service for everyone.

#Step 1: stop everything that might grab the single connection

Single-user mode allows one connection. SQL Agent, monitoring tools, applications or SSMS Object Explorer can take it before you do.

Stop-Service SQLSERVERAGENT -Force
Stop-Service MSSQLSERVER -Force

Stop or block application servers if they reconnect aggressively.

#Step 2: start in single-user mode, allowing only sqlcmd

net start MSSQLSERVER /m"SQLCMD"

/m"SQLCMD" lets only a client whose application name starts with SQLCMD take the connection. Named instance: net start MSSQL$INST01 /m"SQLCMD".

#Step 3: connect as local admin and add an admin

Open an elevated command prompt (Run as administrator, otherwise UAC strips the admin token):

sqlcmd -S . -E

CREATE LOGIN [CONTOSO\DBA-Admins] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\DBA-Admins];
GO
-- Optional: also fix sa as a break-glass account
ALTER LOGIN sa WITH PASSWORD = N'<long random password>';
ALTER LOGIN sa ENABLE;
GO

If the group login exists but was orphaned (group recreated with a new SID), drop and recreate it.

#Step 4: restart normally

net stop MSSQLSERVER
net start MSSQLSERVER
Start-Service SQLSERVERAGENT

Connect with your normal account and check:

SELECT name, type_desc, is_disabled FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;

#Troubleshooting

#Step 5: clean up and prevent

• Store the sa (or break-glass login) password in your password vault, owned by more than one person. Keep it disabled if policy requires, but know how to enable it.
• Give sysadmin to two independent principals (e.g. a DBA group and a break-glass login), so deleting one group doesn't lock everyone out.
• Monitor sysadmin membership changes (server audit on SERVER_ROLE_MEMBER_CHANGE_GROUP).
• Record the incident: who did it, when, why access was lost.

Warning

Local administrators can always do this, which is by design. Treat local admin on database servers as equivalent to sysadmin when you review access.