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.
-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.
-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_*'
Counterpart
Import-sqmDatabaseLogins
Combined use
Sync-sqmDatabaseLogins
Runs before this
Invoke-sqmRestoreDatabase
⬆ Index
All Commands