Commands / Grant-sqmTemporarySysadmin
Grant-sqmTemporarySysadmin
Security sqmSQLTool v1.9.59+ · Grant ✓ -WhatIf supported  ·  ✓ Windows/AD logins and existing SQL-Auth logins
Grants a login temporary sysadmin or dbcreator rights (-Role, default sysadmin) for N days via self-deleting SQL Agent jobs. For patching, installation, and incident-response situations. Both Windows/AD logins (auto-created if missing, via CREATE LOGIN ... FROM WINDOWS) and already-existing SQL-Auth logins are supported; a missing SQL-Auth login raises a clear error instead of being auto-created. 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. No interactive confirmation prompt by default (-TicketNumber already documents the deliberate decision) — pass -Confirm explicitly to re-enable it.
ⓘ  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.
ⓘ  While creating/altering/removing the login, all currently-enabled server-wide DDL triggers and the configured PBM policy are temporarily disabled and restored to their exact prior state — otherwise a Policy-Based Management "On Change: Prevent" policy or a custom DDL trigger on ALTER SERVER ROLE/CREATE LOGIN can silently roll the grant back ("The transaction ended in the trigger."). See Invoke-sqmTempSysadminAction.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEDefault instance of the local computer.
-LoginstringRequired, DOMAIN\Account, an AD group, or an already-existing SQL-Auth login. A missing SQL-Auth login raises an error instead of being auto-created.
-RolestringOptionalsysadminsysadmin or dbcreator.
-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 exists as SQL-Auth login? NO treat as Windows/AD login create via FROM WINDOWS if missing YES, skip auto-create -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 -Role to login (DDL triggers/PBM briefly disabled) ALTER SERVER ROLE [sysadmin|dbcreator] ADD MEMBER Job name already exists? YES -Force? NO → throw NO YES → overwrite Create Revoke-Job on THIS replica Job name: sqmTemp{Role}_{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
dbcreator for an existing SQL-Auth login
Grant-sqmTemporarySysadmin `
    -SqlInstance SQL01 `
    -Login 'app_deploy' `
    -Role dbcreator `
    -Days 1 `
    -TicketNumber 'CHG9001'
⬆ Index
All Commands