Commands / Restore-sqmSysadminAccess
Restore-sqmSysadminAccess
Security sqmSQLTool v1.9.85+ · Restore ✓ -WhatIf supported  ·  ⚠ stops the SQL service temporarily
Emergency recovery for the case where no working sysadmin login exists on an instance at all — every sysadmin account deleted, its password lost, or all sysadmin memberships accidentally revoked. Automates Microsoft's documented recovery procedure: stop the SQL Server service, restart it with the -m"<marker>" startup parameter (single-user mode), use the one connection SQL Server allows in that mode — which grants any local Windows Administrator implicit sysadmin rights regardless of their actual SQL login — to create (or reset the password of and re-enable) the specified SQL-Auth login and add it to the fixed server role sysadmin, then tear the single-user mode back down and restart the service normally. Every step of that teardown runs in a finally block, so the service is put back into normal multi-user operation even if something failed partway through.
ⓘ  Single-user mode allows exactly one connection. Login creation/reset, the sysadmin grant, and verification therefore all run in a single T-SQL batch over a single connection — any extra round-trip would be a window in which another process (a monitoring agent reconnecting the instant the service comes back up, for example) could grab that one slot first and make the whole procedure fail. The same batch also temporarily disables every currently-enabled server-wide DDL trigger and re-enables exactly those afterward, because the built-in syspolicy_server_trigger (Policy-Based Management) can silently roll back CREATE LOGIN/ALTER SERVER ROLE even inside single-user mode — see Invoke-sqmTempSysadminAction for the incident that surfaced this originally.
⚠  Requires Windows-Integrated Authentication to work between the machine running this command and the target instance (that's how SQL Server verifies you're a local Administrator inside single-user mode). Across a workgroup boundary without matching local credentials, or wherever NTLM double-hop is blocked, that authentication fails — run this directly on the affected SQL Server box for it to work reliably, exactly like the real-world procedure it automates.
ⓘ  Unlike Grant-sqmTemporarySysadmin, the interactive Confirm prompt (ConfirmImpact 'High') is not suppressed by default — this is meant as a manual break-glass action performed by someone at the keyboard during an incident, not unattended automation. A pre-flight check also refuses to run (unless -Force) if the instance is already reachable with sysadmin rights under the current Windows account, and a best-effort local check warns/aborts if the target looks like a Windows Server Failover Cluster node, where stopping the service directly (instead of via the cluster resource) can conflict with the cluster service.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEDefault instance of the local computer, or SERVER\INSTANCE.
-LoginstringRequired, SQL-Auth login to grant emergency access. Reset+re-enabled if it already exists, otherwise created.
-PasswordSecureStringOptional, (auto-generated)Without it, a 24-char policy-compliant password is generated (New-sqmRandomSaPassword) and returned in plain text in the output object — never logged.
-TimeoutSecondsintOptional120Max wait (30–900s) for the restarted service to accept the single-user connection.
-TicketNumberstringOptional, Written to the module log and Windows Event Log for traceability.
-ForceswitchSwitch$falseSkips the "already sysadmin reachable" pre-flight check and the local Failover-Cluster warning/abort.

Execution Flow

START Already reachable AND sysadmin, not -Force? YES throw: procedure unneeded nothing changed · -Force overrides NO (expected lockout) Local target looks like an FCI node, not -Force? YES throw: take cluster resource offline first (or -Force) NO -WhatIf or Confirm declined? YES → return, nothing changed -Password provided? NO New-sqmRandomSaPassword 24 chars, policy-compliant YES Stop-DbaService -Type Engine production SQL service goes down here Set-DbaStartupParameter -SingleUser -SingleUserDetails <marker app name> Start-DbaService (single-user mode) Retry until -TimeoutSeconds elapses connect via Windows-Integrated-Auth, ApplicationName = marker ONE T-SQL batch · ONE connection (the only one allowed) 1. Capture + DISABLE TRIGGER ALL ON ALL SERVER 2. Login exists? ALTER LOGIN (reset pwd, ENABLE) : CREATE LOGIN 3. IF NOT direct member → ALTER SERVER ROLE sysadmin ADD MEMBER 4. Re-ENABLE exactly the triggers captured in step 1 5. SELECT LoginExisted, TriggersReEnabled, IsSysadminNow local Administrators get implicit sysadmin in single-user mode IsSysadminNow = 1 (verified)? NO throw caught below → finally still runs YES Log action (WARNING level) module log + Event Log · password NEVER logged error path also reaches finally FINALLY — always runs, even after the throw above Stop-DbaService (best effort) Set-DbaStartupParameter -SingleUser:$false Start-DbaService (normal) → DONE PSCustomObject incl. PasswordPlainText returned

Examples

Emergency access, generated password, with ticket number
Restore-sqmSysadminAccess `
    -SqlInstance SQL01 `
    -Login 'sqm_emergency' `
    -TicketNumber 'INC0099887'
Reset a known login (e.g. sa) to a self-chosen password, skip the pre-flight/cluster checks
$securePw = Read-Host -AsSecureString 'New password'
Restore-sqmSysadminAccess -Login 'sa' -Password $securePw -Force
Local instance (recommended usage: run directly on the affected SQL Server)
Restore-sqmSysadminAccess -Login 'sqm_emergency' -TicketNumber 'INC0099887'
Preview only — shows what would happen, service is never touched
Restore-sqmSysadminAccess -SqlInstance SQL01 -Login 'sqm_emergency' -WhatIf
Longer timeout for a slow-starting instance, then rotate/remove the account afterward
$r = Restore-sqmSysadminAccess -SqlInstance SQL01 -Login 'sqm_emergency' -TimeoutSeconds 300
$r | Select-Object SqlInstance, Login, LoginExisted, Status
# ... regain regular access, then rotate or remove 'sqm_emergency' again ...
⬆ Index
All Commands