Commands / Grant-sqmTemporarySysadmin
Grant-sqmTemporarySysadmin
Security sqmSQLTool v1.8.2+ · Grant ✓ -WhatIf supported  ·  ⚠ Windows/AD logins only
Grants a Windows/AD login temporary sysadmin rights for N days via self-deleting SQL Agent jobs. For patching, installation, and incident-response situations. Without -StartDate: rights are granted immediately, Revoke-Job is scheduled at now + N days. With -StartDate: both Grant-Job and Revoke-Job are scheduled. AlwaysOn-aware by default — creates independent jobs on every replica so rights survive a failover and cleanup runs reliably everywhere.
ⓘ  You can run this against any instance in an AG — the function queries sys.availability_replicas and creates its own local SQL Agent jobs on every replica automatically. To limit to the specified instance only, use -PrimaryOnly. Use -SkipSecondaryServers to exclude individual replicas by name.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEDefault instance of the local computer.
-LoginstringRequired—Must be DOMAIN\Account format. SQL auth logins are rejected immediately.
-DaysintRequired—Duration in days (1–3650). Revoke runs at StartDate + Days (or now + Days).
-StartDatedatetimeOptional— (immediate)Future activation date. If missing or in the past: immediate grant.
-PrimaryOnlyswitchSwitch$falseSkip AG replica detection, only treat specified instance.
-SkipSecondaryServersstring[]Optional@()Replica names to exclude from job creation.
-TicketNumberstringOptional—Written to module log and Windows Event Log for traceability.
-ForceswitchSwitch$falseOverwrite existing Grant/Revoke jobs with identical names.
-SqlCredentialPSCredentialOptional—Only used for immediate inline operations. Agent jobs run under the SQL Agent service account.

Execution Flow

START -Login is DOMAIN\Account? NO throw: SQL-Auth logins not supported — use DOMAIN\Account YES -StartDate future? YES SCHEDULED MODE Create Grant-Job at StartDate Create Revoke-Job at StartDate+Days No inline action now NO IMMEDIATE MODE — grant rights now -PrimaryOnly? YES Only use specified instance NO Query sys.availability_replicas All AG replicas discovered — minus any in -SkipSecondaryServers FOR EACH TARGET REPLICA (parallel, independent) Login exists on this replica? YES Login kept after revoke NO CREATE LOGIN FROM WINDOWS PBM policy briefly disabled → DROP on revoke Grant sysadmin role to login Add-DbaServerRoleMember -ServerRole sysadmin Job name already exists? YES -Force? NO → throw NO YES → overwrite Create Revoke-Job on THIS replica Job name: sqmTempSysadmin_{login}_{instance}_{ts}_Revoke Runs at revocation time  ·  self-deletes after execution Log action Module log + Windows Event Log  ·  TicketNumber included if provided Scheduled mode also merges here DONE — PSCustomObject returned

Examples

Grant immediately for 3 days — all AG replicas
Grant-sqmTemporarySysadmin `
    -SqlInstance SQL01 `
    -Login 'CORP\u.maier' `
    -Days 3 `
    -TicketNumber 'INC0012345'
Scheduled — grant on 1 July 08:00, revoke 2 July 08:00
Grant-sqmTemporarySysadmin `
    -Login 'CORP\u.maier' `
    -Days 1 `
    -StartDate '2026-07-01 08:00' `
    -TicketNumber 'CHG7788'
Preview only — primary instance, no replica jobs
Grant-sqmTemporarySysadmin `
    -SqlInstance SQL01 `
    -Login 'CORP\u.maier' `
    -Days 2 `
    -PrimaryOnly `
    -WhatIf
All replicas except one specific secondary
Grant-sqmTemporarySysadmin `
    -SqlInstance SQL01 `
    -Login 'CORP\u.maier' `
    -Days 5 `
    -SkipSecondaryServers 'SQL03' `
    -Force
⬆ Index
All Commands