Security

Remove-sqmAdOrphanLogin

Removes Windows logins whose Active Directory account no longer exists. Verifies each login against AD before dropping it, writes a rollback script, and skips sysadmins and database owners — designed exclusively for deliberate, supervised use.

Module: sqmSQLTool
Requires: dbatools + ActiveDirectory
ShouldProcess: Yes (High)
Output: PSCustomObject[]

Execution Flow

START ActiveDirectory module available? throw — nothing deleted Yes Get sysadmin login list (abort if query fails) Get-DbaLogin — Windows logins only (WindowsUser / WindowsGroup) foreach Windows login Skip: system patterns / sysadmin / -ExcludeLogin match AD check: Get-ADObject — if query fails → Skip (never delete) Skip: login found in AD (not an orphan) Skip: login owns a database (change owner first) Write rollback script (CREATE LOGIN + server role memberships) ShouldProcess (High): Remove-DbaLogin → Status=Removed Return PSCustomObject[] (all results) DONE
Intentionally NOT designed for unattended use. A missing AD account may be caused by a transient DC or trust issue — not a real orphan. Always run with -WhatIf first to audit what would be removed before committing.
A rollback SQL script (CREATE LOGIN FROM WINDOWS + ALTER SERVER ROLE statements) is written to -BackupPath before each removal. The script is usable only if the AD account is later re-created.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-ExcludeLoginString[]OptionalAdditional logins to protect (wildcards supported).
-AdModuleActionStringOptionalAbort (default) / Install — behavior when the AD module is missing. Skip is treated as Abort for this destructive operation.
-BackupPathStringOptionalDirectory for rollback scripts. Default: C:\System\WinSrvLog\MSSQL.
-SkipBackupSwitchOptionalSkip writing rollback scripts. Not recommended.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning an error status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support (ConfirmImpact=High).

Return Value

Returns an array of PSCustomObject, one per login evaluated, each with: SqlInstance, Login, Status (Removed / Skipped / WhatIf / Failed), Reason, BackupFile, Timestamp.

Examples

Example 1 — Audit what would be removed (safe)

Remove-sqmAdOrphanLogin -SqlInstance "SQL01" -WhatIf

Example 2 — Remove confirmed orphans with exclusion

Remove-sqmAdOrphanLogin -SqlInstance "SQL01" -ExcludeLogin 'DOMAIN\KeepThis*'