Regaining sysadmin Access When Nobody Can Log In

The last sysadmin login has left, the sa password is lost, and Windows administrators are not members of SQL Server's sysadmin role. Regaining sysadmin access is possible from the local machine by starting the instance in single-user mode, connecting with sqlcmd, and adding a controlled Windows group.

Confirm the Lockout Before Regaining sysadmin Access

Check whether another approved sysadmin login or group still exists before stopping the instance. A service restart interrupts applications, scheduled jobs, and availability features. Confirm the instance name, service account, backups, maintenance window, and application owner. Record who authorized the recovery and which Windows group will receive access.

I would not use a random personal account as the permanent repair. A managed administrative group has an owner and membership process. Who will be able to use the restored privilege after the emergency ends? That question belongs in the plan before the server is restarted.

Reserve the Single Connection for Regaining sysadmin Access

Single-user mode allows only one connection. Stop SQL Server Agent first so it cannot take that slot. Use the startup parameter -mSQLCMD to accept the sqlcmd client while excluding SSMS Object Explorer and most other applications. On a default instance, the Database Engine service is MSSQLSERVER; a named instance uses MSSQL$InstanceName.

# PowerShell
# Run in an elevated Windows PowerShell session on the SQL Server host.
Stop-Service -Name 'SQLSERVERAGENT'
Stop-Service -Name 'MSSQLSERVER'
& net.exe start MSSQLSERVER /mSQLCMD

Replace service names for a named instance. Stop application reconnect loops where practical. Some clustered or availability deployments need a different operational procedure; coordinate with the platform owner instead of restarting an arbitrary node. The command is a controlled outage step, not a routine login workaround.

Connect as a Local Administrator

Sign in to Windows with an account that belongs to the computer's local Administrators group. In this recovery mode, that account can connect to the SQL Server instance as sysadmin. Use Windows authentication with sqlcmd and a local server name. Keep only one sqlcmd session open; a second connection can receive the single-user rejection.

REM Command line
sqlcmd -E -S localhost -Q "SELECT SYSTEM_USER AS login_name, IS_SRVROLEMEMBER('sysadmin') AS is_sysadmin;"

For a named instance, use localhost\InstanceName. A one-shot -Q connection closes after the query, leaving the slot free for the next command. Check that the result is 1 before modifying access. If login fails, verify local Administrators membership, service startup parameters, and whether another sqlcmd process holds the connection.

Add the Recovery Group

Create a login for the approved Windows group if it does not exist, then add that login to sysadmin. The example uses a placeholder domain and group. Run the batch through sqlcmd in the single-user session and capture its output. Do not use an unreviewed personal login as the permanent break-glass path.

IF NOT EXISTS
(
    SELECT 1 FROM sys.server_principals
    WHERE name = N'CONTOSO\SQL-DBA-Recovery'
)
    CREATE LOGIN [CONTOSO\SQL-DBA-Recovery] FROM WINDOWS;
ALTER SERVER ROLE sysadmin
ADD MEMBER [CONTOSO\SQL-DBA-Recovery];
GO
SELECT sp.name
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS sp
  ON sp.principal_id = rm.member_principal_id
WHERE rm.role_principal_id = SUSER_ID(N'sysadmin');

Save this T-SQL in a local reviewed .sql file and run it with sqlcmd -E -S localhost -i followed by that file path. The GO is a sqlcmd batch separator, not a T-SQL statement. The Windows group must exist and resolve on the server; otherwise CREATE LOGIN stops with error 15401. Capture errors rather than assuming the role change succeeded.

Restart Normally and Verify Again

Stop the Engine, remove the temporary single-user startup parameter, and start the normal service. If the parameter was supplied only on the net start command line, a normal service start omits it. Restart SQL Server Agent after the Engine is ready. Then sign in as a member of the recovered group and verify sysadmin membership from a fresh normal-mode connection.

# PowerShell
Stop-Service -Name 'MSSQLSERVER'
Start-Service -Name 'MSSQLSERVER'
Start-Service -Name 'SQLSERVERAGENT'

Check the SQL Server error log for normal startup, database recovery, and any missed jobs. An instance left in single-user mode is not recovered for applications. In a named-instance or clustered environment, use the correct service and resource controls and document the exact commands used.

Audit the Path for Regaining sysadmin Access

List all current sysadmin members and remove stale accounts through a separate review. Confirm that the recovery group's membership is tightly controlled and logged. Keep a second approved access route so one person's departure cannot repeat the incident. Test the route during a maintenance exercise and store the procedure where on-call staff can reach it without SQL Server access.

I also record the outage start, the reason the old access failed, the group added, and the time normal service resumed. An emergency fix should leave a clear audit trail. The recovery group is powerful; its ownership and access reviews are as important as the T-SQL that created the login.

Avoid Losing the Slot to Another Client

Single-user mode accepts one connection at a time. An application pool, monitoring agent, or SSMS Object Explorer can connect before the DBA. The uppercase SQLCMD application-name restriction reduces that race. Stop SQL Server Agent and suspend automatic application retries if the maintenance procedure permits it. Do not open multiple sqlcmd windows; even the administrator can block the next administrator command. If the slot is occupied, inspect the local process and service state rather than repeatedly restarting without a plan.

Verify the Group Outside the Emergency Session

After normal restart, test with a Windows account that is actually a member of the restored group. A successful ALTER SERVER ROLE statement proves only that SQL Server accepted the principal. It does not prove the intended people can obtain a Windows token containing that group. Group membership changes can require a new Windows sign-in. Verify both login and role membership from the fresh session, then run a harmless administrative read.

Check whether the new group has a service owner and an access review schedule. A break-glass group with broad permanent membership can turn one lockout into a security problem. I document who can add members, where that action is logged, and how the team will respond if the group itself is unavailable. A procedure for regaining sysadmin access becomes useful only when it is tested and maintained. Schedule a short tabletop exercise after each major instance or identity-system change.

Related reading on this blog: List Users with System Admin (sysadmin) Rights and How to Start Database in a Single User Mode? Interview Question of the Week #202.

Recovered sysadmin access is not the end, it is the start of service and membership checks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.