Security

Invoke-sqmTempSysadminAction

Grants or revokes the sysadmin role for a Windows/AD login on a SQL Server instance. Called by Agent jobs created via Grant-sqmTemporarySysadmin, but can also be used manually for early revocation. Supports self-deleting jobs, policy management, and Windows Event Log auditing.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: No
Output: PSCustomObject

Execution Flow

START dbatools available? No throw error Yes Action? Grant GRANT path Check login exists (sys.server_principals) Login missing? -CreateLoginIfMissing? No → throw Disable PBM policy (-DisablePolicy) CREATE LOGIN [..] FROM WINDOWS Re-enable PBM policy (finally) ALTER SERVER ROLE sysadmin ADD MEMBER Revoke REVOKE path ALTER SERVER ROLE sysadmin DROP MEMBER -RemoveLogin? Other server roles? Yes → keep login No DROP LOGIN (safe) Write-sqmEventLogSafe · sp_delete_job (if -JobName) DONE
All operations are idempotent — granting an already-granted sysadmin role or revoking a non-existent one produce no error. On failure the calling Agent job is NOT deleted so the failure remains visible in job history. The PBM policy is always re-enabled in a finally block even if login creation fails.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL Server instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credentials.
-LoginStringRequiredWindows/AD login (e.g. DOMAIN\u.maier or an AD group).
-ActionStringRequiredGrant or Revoke.
-CreateLoginIfMissingSwitchOptionalGrant only: creates the AD login via CREATE LOGIN … FROM WINDOWS if not present.
-RemoveLoginSwitchOptionalRevoke only: drops the login after revocation — only if it holds no other server roles (safety check).
-DisablePolicyBoolOptionalDisable the configured PBM policy before creating the login. Default: $true.
-TicketNumberStringOptionalTicket/order number included in all log and Event Log entries.
-JobNameStringOptionalName of the calling Agent job. Deleted via sp_delete_job on success (self-deleting job pattern).

Return Value

Returns PSCustomObject: SqlInstance, Login, Action, LoginCreated, LoginRemoved, TicketNumber, JobDeleted, Status, Message, Timestamp. On failure, throws so the calling Agent job records the error.

Examples

Example 1 — Manual early revocation

Invoke-sqmTempSysadminAction -SqlInstance SQL01 -Login 'DOM\u.maier' -Action Revoke

Example 2 — Revoke and remove login

Invoke-sqmTempSysadminAction -Login 'DOM\u.maier' -Action Revoke -RemoveLogin

Example 3 — Grant with auto-create login (from Agent job)

Invoke-sqmTempSysadminAction -SqlInstance SQL01 -Login 'DOM\u.maier' -Action Grant -CreateLoginIfMissing -TicketNumber "TKT-4711" -JobName "sqmGrant_DOM_u.maier"