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

You are about to move logins to a new server, or you just did and something cannot log in. These 2 scripts cover both ends: Get-MigrationLoginAudit tells you what each login needs on the target, and Get-PostMigrationValidation gives you 13 counts and settings to run on both servers and diff, so you know whether the move landed.

When to Run These Scripts

• Get-MigrationLoginAudit, while you plan, before you write any login scripts
• Get-PostMigrationValidation, straight after cutover, on source and target, before you call the migration done
• Get-PostMigrationValidation again a few days later, to catch configuration drift once real traffic is on the target
• Get-MigrationLoginAudit alongside Permissions and Role Membership when the migration also needs a full access review
• For readiness checks before the move, start with Version Upgrade Readiness and Migration Risk Assessment

The Scripts

Get-MigrationLoginAudit: What Each Login Needs

/*
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 DEFINITION (sysadmin and securityadmin have it implicitly)
*/
-- 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 specific to each login type, so the output can go straight into your migration runbook.

Get-PostMigrationValidation: Counts to Diff Between Servers

/*
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 DEFINITION, VIEW ANY DATABASE, VIEW SERVER STATE, db_datareader on msdb
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

/*
  DESIGN: Returns a single summary result set with one row per check category.
  Run this script on both servers. Export as CSV, then diff the two outputs.
  The 'value' column should match between source and target for each check_name.

  Checks performed:
    - user_database_count — total number of user databases
    - databases_not_online — any database not in ONLINE state
    - databases_missing_backup — databases with no recorded backup (should be 0 on target if just restored)
    - total_login_count — server principal count (SQL + Windows)
    - sql_login_count — SQL-authenticated logins
    - windows_login_count — Windows logins and groups
    - sysadmin_count — members of sysadmin role
    - linked_server_count — linked server definitions
    - agent_job_count — SQL Agent jobs
    - instance_version — SQL Server version string (major.minor.build)
    - instance_edition — SQL Server edition
    - max_server_memory_mb — sp_configure max server memory
    - maxdop — sp_configure max degree of parallelism
    - tempdb_data_file_count — TempDB data files (should match after setup)
*/

SELECT
    check_name,
    value,
    detail
FROM (

    -- Database count
    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

    -- Databases not ONLINE
    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

    -- Login count (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\%'

    UNION ALL

    -- SQL login count
    SELECT
        'sql_login_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'type = S (SQL authenticated)'
    FROM sys.server_principals
    WHERE type = 'S'
      AND name NOT LIKE N'##%##'
      AND name NOT IN (N'sa')

    UNION ALL

    -- Windows login count
    SELECT
        'windows_login_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'type IN (U, G) - Windows users and groups'
    FROM sys.server_principals
    WHERE type IN ('U', 'G')
      AND name NOT LIKE N'NT SERVICE\%'
      AND name NOT LIKE N'NT AUTHORITY\%'
      AND name NOT LIKE N'BUILTIN\%'

    UNION ALL

    -- Sysadmin count
    SELECT
        'sysadmin_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'members of sysadmin fixed server role'
    FROM sys.server_role_members srm
    INNER JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
    INNER JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id
    WHERE r.name = N'sysadmin'
      AND m.name NOT LIKE N'##%##'
      AND m.name NOT IN (N'sa')

    UNION ALL

    -- Linked servers
    SELECT
        'linked_server_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'sys.servers WHERE is_linked = 1'
    FROM sys.servers WHERE is_linked = 1

    UNION ALL

    -- SQL Agent jobs
    SELECT
        'agent_job_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'msdb.dbo.sysjobs'
    FROM msdb.dbo.sysjobs

    UNION ALL

    -- Version
    SELECT
        'instance_version',
        CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(200)),
        CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(200))

    UNION ALL

    -- Edition
    SELECT
        'instance_edition',
        CAST(SERVERPROPERTY('Edition') AS nvarchar(200)),
        CAST(SERVERPROPERTY('EngineEdition') AS nvarchar(200))

    UNION ALL

    -- Max server memory
    SELECT
        'max_server_memory_mb',
        CAST(CAST(value_in_use AS bigint) AS nvarchar(200)),
        'sp_configure ''max server memory (MB)'''
    FROM sys.configurations WHERE name = 'max server memory (MB)'

    UNION ALL

    -- MAXDOP
    SELECT
        'maxdop',
        CAST(value_in_use AS nvarchar(200)),
        'sp_configure ''max degree of parallelism'''
    FROM sys.configurations WHERE name = 'max degree of parallelism'

    UNION ALL

    -- TempDB data file count
    SELECT
        'tempdb_data_file_count',
        CAST(COUNT(*) AS nvarchar(200)),
        'sys.master_files WHERE database_id = 2 AND type = 0'
    FROM sys.master_files
    WHERE database_id = 2 AND type = 0

) chk
ORDER BY check_name;

Every row has the same 3 columns, check_name, value and detail, so the output diffs cleanly however many checks get added.

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:

• sql/migration/Get-MigrationLoginAudit.sql
• sql/migration/Get-PostMigrationValidation.sql

Example Output

Get-MigrationLoginAudit

One run on the lab instance returned 32 principals: 20 SQL logins, 1 Windows login, 1 certificate login and 10 server roles. These are 10 of them, with is_expiration_checked and migration_action left out for width.

What the audit does not tell you:

• Role membership. Every active SQL login reads MEDIUM, including Lab1005_mig_admin, which sits in dbcreator and a custom server role. Take roles from Permissions and Role Membership before you script.
• A missing default database. Lab1005_mig_nodb has a default database that no longer exists, and nothing in the row says so. Signing in as it failed with “Cannot open user default database. Login failed.”
• Asymmetric key logins. The lab had one and it is not in the output at all. The script reads types S, U, G, C and R, and that login is type K.

Get-PostMigrationValidation

On a real migration you run this on the source and the target and diff the two outputs. The lab run below is one instance, so it shows one side.

Diff the value column, not detail. instance_version’s detail reads RTM on a CU8 instance, because SERVERPROPERTY('ProductLevel') only reports RTM or a service pack.

The login counts match on count, not on identity. Dropping one login and creating another between two runs left total_login_count unchanged. To prove the same logins came across, with the same SIDs, run Login Migration Parity on both servers.

Understanding the Results

Best Practices

• Put sa and every disabled login in the migration runbook as their own line items. Both are easy to get subtly wrong on a target, and a disabled account migrated as enabled, or the reverse, is an avoidable mistake
• Run both scripts on source and target under logins with the same permissions, or the counts will not compare
• Add a check to Post-Migration Validation when a migration surprises you. Each check is one more UNION ALL row with its own check_name

Microsoft’s reference covers sys.server_principals, sp_configure and sys.databases in full.

Related Scripts

You may also find these scripts useful:

• Version Upgrade Readiness
• Migration Risk Assessment
• Permissions and Role Membership
• Linked Servers
• Fix Orphaned Users
• Login Migration Parity, login by login and SID by SID, where Post-Migration Validation only counts
• Generate Login Script, the SID-preserving script the MEDIUM rows point at
• Fix “Msg 207: Invalid Column Name”
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Different login types need different migration steps, and a migration is only done when the numbers match on both servers. Run Migration Login Audit before you script the logins, and Post-Migration Validation on both servers straight after cutover.