· Uwe Janke
Commands / Export-sqmDatabaseLogins

Export-sqmDatabaseLogins

Security sqmSQLTool v1.9.78+ · Export ✓ -WhatIf supported  ·  ⚠ sysadmin/sa logins are never exported, no override
Exports the SQL Server authentication logins backing a database's users as a self-contained, idempotent T-SQL script (password hash + SID + policy flags), for later application on a different instance via Import-sqmDatabaseLogins. Built for databases like an application copy that is periodically refreshed from Production to Test (e.g. one with hundreds or thousands of SQL logins): the restore itself brings the database users over correctly, but the matching server-level logins - and in particular their current password - normally live only on Production and drift out of sync on Test as passwords change there over time. Only -SqlInstance (the source) is ever contacted; getting the generated file to wherever Import-sqmDatabaseLogins will read it is a separate, external transport this function does not perform.
ⓘ  Sysadmin/'sa' logins (detected dynamically via the well-known SID 0x01 and is_srvrolemember('sysadmin', ...), independent of any rename) are excluded from every export with no override switch. The generated script repeats this check a second time, inside every single per-login block, so it stays safe even if hand-run against the wrong instance later.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringRequired, Source SQL Server instance (Production). Only this instance is ever contacted.
-SqlCredentialPSCredentialOptional, Credential for -SqlInstance.
-DatabasestringRequired, Database whose SQL-authentication users' logins should be exported.
-OutputPathstringRequired, Full file path, or an existing/creatable directory (auto-named DatabaseLogins_<Database>_<SqlInstance>_<timestamp>.sql inside it). Any path is accepted - local disk, UNC share, etc. The directory form is meant for automation: a new timestamped file per run, written as .tmp first and renamed when complete, so a copy job never picks up a half-written file.
-Loginstring[]Optional, (all)Restricts the export to these login names (wildcards allowed).
-ExcludeLoginstring[]Optional, Additional logins to exclude (wildcards allowed), e.g. a shared service account that should keep an independent Test-side password.
-KeepLatestintOptional5Directory form only (v1.9.161+): after a successful write, keep this many of the newest exports for the same database and source instance and delete the older ones (they contain password hashes). Matched by file header, not file name. 0 keeps everything.
-LatestCopyswitchSwitch$falseDirectory form only (v1.9.224+): additionally writes the same content as DatabaseLogins_<Database>_<SqlInstance>_latest.sql under a constant name (atomic via .tmp, overwritten on every run). For copy jobs and tools that need a fixed file name, or Import-sqmDatabaseLogins -InputPath <full path of the _latest file>. -KeepLatest never counts or deletes it, and the import's directory mode ignores it (it only picks files with a timestamp). With a full file path as -OutputPath the switch has no effect (warning). The result object has an additional LatestFile property.
-EnableExceptionswitchSwitch$falseThrow terminating exceptions immediately instead of returning a Failed result object.

Execution Flow

START 1. Connect to -SqlInstance (Production) only Read-only - no writes ever happen on the source 2. Enumerate SQL-auth users of -Database sys.database_principals, type = 'S'  ·  excludes dbo/guest/sys 3. Exclude sysadmin / sa / system logins - no override sa via well-known SID 0x01, plus dynamic is_srvrolemember('sysadmin') check Apply -Login / -ExcludeLogin wildcard filters No logins remain after exclusion/filters? YES Warning + Skipped nothing written NO 4. Load SID + password hash from sys.sql_logins + CHECK_POLICY / CHECK_EXPIRATION / default database 5. Build ONE idempotent, sysadmin-guarded block per login Same SID exists → ALTER password only  ·  same name, different SID → DROP+CREATE sysadmin/sa re-checked again inside the block itself - never touched, even there 6. Resolve -OutputPath (file or directory) Write self-contained, human-readable .sql (UTF-8, no BOM) Header records source, database, timestamp, login count DONE, result object returned (LoginCount, OutputFile, ...)

Examples

Export every SQL-auth login backing Frontarena to a share for an external cross-domain copy job
Export-sqmDatabaseLogins `
    -SqlInstance 'ProdSQL' `
    -Database 'Frontarena' `
    -OutputPath '\\Share\Handover\Frontarena_Logins.sql'
Export into an auto-named file, skipping a shared service account that keeps its own Test password
Export-sqmDatabaseLogins `
    -SqlInstance 'ProdSQL' `
    -Database 'Frontarena' `
    -OutputPath 'C:\Temp' `
    -ExcludeLogin 'SvcAccount_*'
Scheduled export via the AG listener (not the AG name), keeping only the three newest files
Export-sqmDatabaseLogins `
    -SqlInstance 'ProdAgListener' `
    -Database 'Frontarena' `
    -OutputPath '\\Share\Handover' `
    -KeepLatest 3
Counterpart
Import-sqmDatabaseLogins
Combined use
Sync-sqmDatabaseLogins
Runs before this
Invoke-sqmRestoreDatabase
⬆ Index
All Commands