DBA Scripts: Get Migration Login Audit and Post-Migration Validation

May 12, 2026

–

Part of the DBA-Tools Project.

Before the Migration: What to Script. After: Did It Actually Work.

Version Upgrade Readiness and Migration Risk Assessment cover getting ready. This post covers the two bookends of the migration itself: what needs scripting before you start, and how do you confirm the target actually matches the source once you’re done.

Migration Login Audit goes through every server-level principal, SQL logins, Windows logins and groups, certificate-backed logins, server roles, and tells you the specific migration action each one needs, not just a flat inventory. Post-Migration Validation runs the same summary query on both source and target, so you diff two CSVs instead of manually cross-checking a dozen settings by eye.

Why Migration Login Audit and Post-Migration Validation Matter

Not every login migrates the same way, a SQL login needs its SID preserved to keep permissions intact, a Windows login just needs the target domain to resolve it, a certificate-backed login needs the certificate exported and recreated first
sa and any disabled logins are easy to handle wrong on a target server, silently migrating a disabled account as enabled (or vice versa) is a real, avoidable mistake
“The migration is done” is a claim, not a fact, until something actually confirms database count, login count, and key configuration match between source and target
A manual side-by-side comparison of a dozen settings is exactly the kind of check that’s easy to do sloppily under deadline pressure, a single diffable summary removes the judgment calls

When to Run These Scripts

Migration Login Audit: early in migration planning, before writing any login-scripting automation, so you know exactly what each login type needs
Post-Migration Validation: immediately after cutover, on both source and target, before declaring the migration complete
Post-Migration Validation again a few days later, to confirm nothing configuration-related drifted once real traffic hit the target
Migration Login Audit alongside Permissions and Role Membership when the migration also needs a full access review

The Scripts

Get-MigrationLoginAudit — What Each Login Needs, Not Just a List

/*
Script Name : Get-MigrationLoginAudit
Category    : migration
Purpose     : Audits all server-level principals that need to be migrated — SQL logins, Windows logins, and server roles — with migration risk and action per login type.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-migration-login-audit-and-post-migration-validation/)
Requires    : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-migration-login-audit-and-post-migration-validation/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    sp.name                         AS login_name,
    sp.type_desc                    AS login_type,
    sp.default_database_name,
    sp.is_disabled,
    sl.is_policy_checked,
    sl.is_expiration_checked,
    CASE
        WHEN sp.name = 'sa'              THEN 'HIGH   — document or reset sa password; confirm enabled state is intentional'
        WHEN sp.type = 'S'
             AND sp.is_disabled = 0      THEN 'MEDIUM — active SQL login; script with SID preserved using Generate-LoginScript.ps1'
        WHEN sp.type = 'S'
             AND sp.is_disabled = 1      THEN 'LOW    — disabled SQL login; migrate or exclude intentionally'
        WHEN sp.type IN ('U', 'G')       THEN 'LOW    — Windows auth; no password migration needed'
        WHEN sp.type = 'C'               THEN 'HIGH   — certificate-backed login; script cert and login together'
        ELSE                                  'INFO'
    END                             AS migration_risk,
    CASE sp.type
        WHEN 'S' THEN 'Script with SID: Generate-LoginScript.ps1 — review output before running on target'
        WHEN 'U' THEN 'Windows user — verify AD account is accessible from target server domain/trust'
        WHEN 'G' THEN 'Windows group — verify group is accessible from target server domain/trust'
        WHEN 'C' THEN 'Certificate-backed — export certificate from master and recreate on target first'
        WHEN 'R' THEN 'Server role — verify role definition exists on target (custom roles only)'
        ELSE          'Review manually'
    END                             AS migration_action
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sl.principal_id = sp.principal_id
WHERE sp.type IN ('S', 'U', 'G', 'C', 'R')
  AND sp.name NOT LIKE '##%'
  AND sp.name NOT LIKE 'NT AUTHORITY\%'
  AND sp.name NOT LIKE 'NT SERVICE\%'
  AND sp.name NOT LIKE 'BUILTIN\%'
ORDER BY
    CASE sp.type WHEN 'S' THEN 1 WHEN 'U' THEN 2 WHEN 'G' THEN 3 ELSE 4 END,
    sp.is_disabled,
    sp.name;

migration_action is deliberately specific per login type rather than a generic “migrate this login” note, so the output can go straight into a migration runbook.

Get-PostMigrationValidation — Diff Two CSVs, Not a Dozen Settings by Eye

/*
Script Name : Get-PostMigrationValidation
Category    : migration
Purpose     : Run on both SOURCE and TARGET and compare the CSV outputs to confirm the
              migration is complete and consistent. Surfaces database count mismatches,
              databases not ONLINE, orphaned users, and login count deltas.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-migration-login-audit-and-post-migration-validation/)
Requires    : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-migration-login-audit-and-post-migration-validation/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    check_name,
    value,
    detail
FROM (
    SELECT 'user_database_count' AS check_name,
        CAST(COUNT(*) AS nvarchar(200)) AS value,
        'databases with database_id > 4' AS detail
    FROM sys.databases WHERE database_id > 4

    UNION ALL

    SELECT 'databases_not_online',
        CAST(COUNT(*) AS nvarchar(200)),
        ISNULL(STUFF((
            SELECT N', ' + name + N' (' + state_desc + N')'
            FROM sys.databases
            WHERE database_id > 4 AND state_desc <> N'ONLINE'
            FOR XML PATH(''), TYPE).value('.','nvarchar(max)'), 1, 2, N''),
            N'All ONLINE')
    FROM sys.databases WHERE database_id > 4 AND state_desc <> N'ONLINE'

    UNION ALL

    SELECT 'total_login_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'SQL + Windows logins (excl. system accounts)'
    FROM sys.server_principals
    WHERE type IN ('S', 'U', 'G')
      AND name NOT LIKE N'##%##' AND name NOT IN (N'sa')
      AND name NOT LIKE N'NT SERVICE\%' AND name NOT LIKE N'NT AUTHORITY\%'
      AND name NOT LIKE N'BUILTIN\%'

    -- ... additional checks: sql_login_count, windows_login_count, sysadmin_count,
    --     linked_server_count, agent_job_count, instance_version, instance_edition,
    --     max_server_memory_mb, maxdop, tempdb_data_file_count

) chk
ORDER BY check_name;

Every row follows the same shape, check_name, value, detail, deliberately, so the output diffs cleanly regardless of which specific checks get added to it over time.

How To Run From The Repo

Clone DBA Tools, initialize and run either script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Audit every server-level principal and its specific migration action:
.\run.ps1 Get-MigrationLoginAudit

# Run on BOTH source and target, then diff the two CSVs:
.\run.ps1 Get-PostMigrationValidation

# To run against a remote sql server:
.\run.ps1 Get-MigrationLoginAudit -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

Real output from this lab instance, not staged. Get-MigrationLoginAudit (11 principals, condensed):

login_name	login_type	is_disabled	migration_risk
sa	SQL_LOGIN	False	HIGH, document or reset sa password; confirm enabled state is intentional
DemoDisabledAdmin	SQL_LOGIN	True	LOW, disabled SQL login; migrate or exclude intentionally
DBA_ServiceAccount	SQL_LOGIN	False	MEDIUM, active SQL login; script with SID preserved using Generate-LoginScript.ps1
HPAI01\Peter	WINDOWS_LOGIN	False	LOW, Windows auth; no password migration needed

Get-PostMigrationValidation, run on this instance alone (would be run again on a target and diffed):

check_name	value	detail
user_database_count	20	databases with database_id > 4
databases_not_online	0	All ONLINE
total_login_count	10	SQL + Windows logins
sysadmin_count	10	members of sysadmin fixed server role
instance_version	17.0.4045.5	RTM
maxdop	8	sp_configure ‘max degree of parallelism’

sysadmin_count = 10 on this lab box is a genuine, real number, expected for a shared development environment, but exactly the kind of value worth confirming intentional on a production target rather than assumed.

Understanding the Results

Migration Login Audit:
– migration_risk = HIGH on sa — always worth a specific line item in the migration runbook, not folded into the general SQL login count
– is_policy_checked / is_expiration_checked — only populated for SQL logins (NULL for Windows logins and roles, since password policy doesn’t apply to them); worth confirming these match your target’s policy expectations, not just copied blindly
– migration_action for type = C (certificate-backed) — the certificate itself has to be exported from master and recreated on the target before the login script will succeed, an easy step to miss

Post-Migration Validation:
– Any check_name where source and target values differ — that’s the finding; the script itself doesn’t diff, running it on both servers and comparing is the workflow
– databases_not_online not “All ONLINE” on the target — stop and investigate before calling the migration complete, a database that didn’t come online cleanly needs attention now, not after cutover
– sysadmin_count differing between source and target — often means the migration script list didn’t fully replicate role memberships, worth a specific follow-up check

Best Practices

Run Migration Login Audit early enough to inform the actual login-scripting automation, not as an afterthought once scripts are already written
Treat sa and disabled logins as explicit line items in a migration runbook, both are easy to get subtly wrong on a target
Run Post-Migration Validation on both source and target immediately after cutover, and again a few days later once real traffic has hit the target
Keep the check list in Post-Migration Validation growing as new migration surprises get found, the UNION ALL structure makes adding a new check_name straightforward

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why does Migration Login Audit give a different action for each login type instead of one generic script?

Because the actual migration steps are genuinely different: a SQL login needs its SID preserved to keep database permissions intact after migration, a Windows login just needs the target domain to resolve the account, and a certificate-backed login needs an entirely separate certificate export and recreation step. Treating them all the same is how migration scripts miss real steps.

Why doesn’t Post-Migration Validation do the diffing itself?

It’s designed to run identically on both source and target, independent of each other, so the only shared logic is the check list itself. Diffing two CSVs (by hand, in a spreadsheet, or with a script) keeps the validation query simple and makes it obvious exactly which check_name values don’t match.

Summary

Migrating logins correctly means treating different login types differently, not running one script and hoping. Confirming a migration actually succeeded means comparing real numbers between source and target, not trusting that “it looked fine.” These two scripts cover both ends: what to script, and how to know it worked.

Run Migration Login Audit before writing your login migration automation, and run Post-Migration Validation on both servers immediately after cutover, and again a few days later once real traffic has hit the target.

Migration

Blog Categories

Comments

Leave a Reply Cancel reply