Commands / Copy-sqmLogins
Copy-sqmLogins
Security sqmSQLTool v1.8.2+ · Copy ✓ -WhatIf supported  ·  ⚠ Policy re-enabled via finally block
Copies SQL and Windows logins from a source SQL Server instance to a target instance. Process: (1) disable policy on target, (2) check and optionally align authentication mode, (3) load and filter logins (system logins, sysadmin logins including renamed sa, AD orphans are excluded), (4) AD-validate all Windows logins — unresolvable accounts are skipped and reported as AdOrphan, (5) copy via Copy-DbaLogin with password hash and SID mapping, (6) repair orphaned users on all target databases, (7) re-enable policy — guaranteed via finally block even on unhandled errors.
ⓘ  The sa account is always excluded regardless of -IncludeSystemLogins — it is detected via its well-known SID 0x01, so renamed sa accounts are safely excluded too. If source uses Mixed Mode and target is Windows-only, the function aborts unless -AdjustAuthMode is specified.

Parameters

ParameterTypeRequiredDefaultNotes
-SourcestringRequired—Source SQL Server instance.
-DestinationstringRequired—Target SQL Server instance.
-SqlCredentialPSCredentialOptional—Used for both source and target. For different credentials use -SourceCredential / -DestinationCredential.
-SourceCredentialPSCredentialOptional—Credential specifically for the source instance.
-DestinationCredentialPSCredentialOptional—Credential specifically for the target instance.
-Loginstring[]Optional— (all)Filter: only copy these logins (wildcards allowed).
-ExcludeLoginstring[]Optional—Logins to exclude from the copy (wildcards allowed).
-IncludeSystemLoginsswitchSwitch$falseAlso copies NT SERVICE\*, NT AUTHORITY\*, BUILTIN\* logins. The sa account (SID 0x01) is ALWAYS excluded regardless of this switch.
-DisablePolicyboolOptional$trueDisables the configured DefaultPolicy on the target before copying and re-enables it afterwards via finally block.
-AdjustAuthModeswitchSwitch$falseSwitches target to Mixed Mode if source is Mixed Mode and target is Windows-only. Without this switch the function aborts on mode mismatch.
-RestartServiceIfRequiredswitchSwitch$falseAutomatically restarts the SQL Server service on the target after an auth mode change.
-ForceswitchSwitch$falseOverwrites existing logins on the target.
-AdModuleActionstringOptionalInstallBehavior when ActiveDirectory module is missing: Install (auto-install), Skip (skip AD check), Abort (error).
-ContinueOnErrorswitchSwitch$falseContinue with next login on error instead of aborting.
-EnableExceptionswitchSwitch$falseThrow terminating exceptions immediately.

Execution Flow

START 1. Disable DefaultPolicy on target (if -DisablePolicy $true) $policyWasDisabled flag set — finally block re-enables it guaranteed Source = Mixed Mode Target = Windows-Only? YES -AdjustAuthMode YES → switch to Mixed NO → throw & abort NO 3. Load logins from source — apply filters Exclude: sa (SID 0x01)  ·  all sysadmins  ·  ##MS_*  ·  NT SERVICE\*  ·  NT AUTHORITY\*  ·  -ExcludeLogin patterns Apply -Login filter  ·  IncludeSystemLogins overrides NT SERVICE/ AUTHORITY (but NEVER sa) No logins after filter? YES Warning + return empty result set NO 4. AD-validate Windows logins (WindowsUser / WindowsGroup) Get-ADObject per SamAccountName  ·  Not found → AdOrphan (removed from batch) AD query fails → AdQueryFailed (also skipped)  ·  No AD module → behavior per -AdModuleAction AD module missing? YES Install → try install Skip → skip AD check Abort → throw error NO 5. Copy-DbaLogin — password hash + SID mapping All remaining logins copied to target  ·  -Force overwrites existing Per-login result: Success / Skipped / Failed added to result list 6. Repair-DbaDbOrphanUser on all target user databases Runs on all databases in Status = Normal  ·  Always runs (no switch)  ·  Skipped if no user DBs Repairs user-to-login mapping broken by SID drift FINALLY — Re-enable DefaultPolicy on target Runs always — even after unhandled exceptions  ·  Only if $policyWasDisabled = $true Errors in finally: logged only, never rethrown (would mask original error) Summary: Success / Failed / AdOrphan counts Write-Host summary line + return $results list DONE — List[PSCustomObject] returned

Examples

Copy all non-system logins — AD check and orphan repair included
Copy-sqmLogins -Source 'SQL01' -Destination 'SQL02'
Copy with auth mode alignment and automatic service restart
Copy-sqmLogins `
    -Source 'SQL01' `
    -Destination 'SQL02' `
    -AdjustAuthMode `
    -RestartServiceIfRequired
Copy only App_ logins, overwrite existing, preview with -WhatIf
Copy-sqmLogins `
    -Source 'SQL01' `
    -Destination 'SQL02' `
    -Login 'App_*' `
    -Force `
    -WhatIf
Copy without policy handling; abort on AD module missing
Copy-sqmLogins `
    -Source 'SQL01' `
    -Destination 'SQL02' `
    -DisablePolicy $false `
    -AdModuleAction Abort `
    -EnableException
⬆ Index
All Commands