DBA Scripts: Get Login Migration Parity

---

Comparing Logins Between Two Servers, Attribute by Attribute

After a migration the logins on the target usually look right. The names are all there, the
count matches, and the application connects. Then a week later somebody finds that a
decommissioned account is enabled again, or a report that runs on the first of the month fails
because a login’s default database was never created on the new server.

The reason those get through is that most migration checks count logins. A count is the one thing
that survives almost every mistake you can make: recreate every login by name with fresh SIDs and
no password policy, and the count still matches. This script compares the attributes instead. Run
it on the source, run it on the target, diff the two CSVs, and the differences are the migration
defects.

---

Why Login Parity Matters

• A login recreated by name gets a brand new SID, so every database user mapped to the old one
  is orphaned. This is the single most common migration defect and it presents as a permissions
  problem, not as a migration problem
• CREATE LOGIN always produces an enabled login. There is no syntax for creating one
  disabled, so a deliberately disabled account arrives on the target live, with its original
  password still valid
• A default database that does not exist on the target stops the login connecting at all, and
  the error you get back reads like an authentication failure
• Password policy silently relaxing is a security finding, not a footnote, and nothing warns you
• This is a comparison, not a fixer. It changes nothing. It gives you two CSVs and a diff

---

When to Run This Script

• Immediately after any migration, restore-to-new-server, or DR build, before you hand it over
• Before a cutover, on the source, so you have a fingerprint to compare against afterwards
• When an application authenticates fine on the old server and fails on the new one
• Periodically against a DR replica that is supposed to mirror production’s logins
• After anyone has run a login-transfer script, including this repo’s own generator

---

The Script

Read-only, no cursors, no temp tables, and no connection between the two servers. That last part is
deliberate: run it twice and diff, and it works across an air gap with no linked server to set up.

/*
Script Name : Get-LoginMigrationParity
Category    : migration
Purpose     : Compare logins between two servers to verify a migration. Returns one comparable
              fingerprint row per login (SID, disabled state, deny connect, default database
              and language, password policy, password hash, server roles). Run on SOURCE and
              TARGET, export both as CSV, then diff. Catches what a login migration silently
              drops and a login count check cannot see.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-migration-parity/)
Requires    : VIEW ANY DEFINITION (VIEW SERVER STATE for full detail); CONTROL SERVER to
              compare password hashes, which are otherwise reported as 'no-permission'
Notes       : Get-PostMigrationValidation.sql compares COUNTS. A count matches while every
              login on the target is enabled, mapped to the wrong default database and
              carrying a fresh SID, so a count check passes a migration that is wrong.
              This script compares the attributes themselves.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/*
  DESIGN: deliberately one flat, ordered, deterministic row per login so a plain text diff
  of two CSVs is the whole comparison. No cursors, no temp tables, no server-to-server
  connection: run it twice and diff, which works across an air gap and needs no linked server.

  Columns chosen because each one is something a real migration loses quietly:
    sid_hex           - a login recreated BY NAME gets a fresh SID, orphaning every database
                        user mapped to it. The single most common migration defect.
    is_disabled       - CREATE LOGIN always produces an ENABLED login. A disabled account
                        comes back live on the target with its original password valid.
    connect_denied    - an explicit DENY CONNECT SQL is a separate object from is_disabled
                        and is not carried by CREATE LOGIN either.
    default_database  - if it does not exist on the target the login cannot connect at all.
    default_language  - changes date parsing and error message language for that session.
    check_policy /
    check_expiration  - password policy silently relaxing on the target is a security finding.
    password_hash_id  - short fingerprint of the stored hash, so you can compare without
                        putting a full hash in a CSV that gets emailed around. Read it
                        carefully: the hash is SALTED, so the same password typed twice
                        produces two different hashes. A matching fingerprint therefore
                        proves the login was scripted WITH PASSWORD = <hash> HASHED and the
                        hash carried across. A differing one means somebody re-typed the
                        password, even if they typed the same password.
    server_roles      - fixed and user-defined server role membership, comma separated.

  HOW TO USE
    1. Run on SOURCE, save CSV.
    2. Run on TARGET, save CSV.
    3. Diff. Every row should match except password_hash_id for Windows logins (always 'n/a').
    4. Rows present on source and missing on target are logins that did not migrate.

  This script never writes. Nothing here changes a login.
*/

SELECT
    p.name                                             AS login_name,
    p.type_desc                                        AS login_type,

    -- A login recreated by name gets a new SID. This column is the one that matters most.
    CONVERT(nvarchar(200), p.sid, 1)                   AS sid_hex,

    p.is_disabled,

    -- DENY CONNECT SQL is a permission, not a flag on the principal, so it is easy to miss.
    CASE WHEN EXISTS (
            SELECT 1
            FROM sys.server_permissions sp
            WHERE sp.grantee_principal_id = p.principal_id
              AND sp.permission_name      = N'CONNECT SQL'
              AND sp.state                = 'D')      -- D = DENY
         THEN 1 ELSE 0 END                             AS connect_denied,

    ISNULL(p.default_database_name, N'(none)')         AS default_database,

    -- Does that default database actually exist here? Blank on the target means the login
    -- is created but cannot connect, which reads as an authentication problem.
    CASE WHEN p.default_database_name IS NULL THEN 'n/a'
         WHEN DB_ID(p.default_database_name) IS NULL THEN 'MISSING'
         ELSE 'present' END                            AS default_db_state,

    ISNULL(p.default_language_name, N'(none)')         AS default_language,

    CASE WHEN p.type = 'S' THEN CAST(sl.is_policy_checked     AS nvarchar(5)) ELSE 'n/a' END AS check_policy,
    CASE WHEN p.type = 'S' THEN CAST(sl.is_expiration_checked AS nvarchar(5)) ELSE 'n/a' END AS check_expiration,

    -- Fingerprint, not the hash: enough to prove the password came across, safe to share.
    CASE WHEN p.type <> 'S' THEN 'n/a'
         WHEN sl.password_hash IS NULL THEN 'no-permission'
         ELSE RIGHT(CONVERT(nvarchar(300), sl.password_hash, 1), 12)
    END                                                AS password_hash_id,

    STUFF((
        SELECT N', ' + r.name
        FROM sys.server_role_members srm
        INNER JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
        WHERE srm.member_principal_id = p.principal_id
        ORDER BY r.name
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'')  AS server_roles,

    CONVERT(varchar(19), p.create_date, 120)           AS create_date,
    CONVERT(varchar(19), p.modify_date, 120)           AS modify_date
FROM sys.server_principals AS p
LEFT JOIN sys.sql_logins   AS sl ON sl.principal_id = p.principal_id
-- 'S' = SQL_LOGIN, 'U' = WINDOWS_LOGIN, 'G' = WINDOWS_GROUP.
-- There is no type 'W'; using one silently returns no Windows logins at all.
WHERE p.type IN ('S', 'U', 'G')
  AND p.name NOT LIKE N'##%##'          -- certificate-backed internal principals
  AND p.name NOT LIKE N'NT SERVICE\%'   -- belong to the target's own installation
  AND p.name NOT LIKE N'NT AUTHORITY\%'
  AND p.name NOT LIKE N'BUILTIN\%'
ORDER BY p.name;

---

How To Run From The Repo

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

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

# Local instance, results in the terminal:
.\run.ps1 Get-LoginMigrationParity

# The real workflow: CSV from each server, then diff
.\powershell\wrappers\migration\Get-LoginMigrationParity.ps1 -ServerInstance SOURCE -OutputFormat Csv -OutputPath .\output-files\migration\source-logins.csv
.\powershell\wrappers\migration\Get-LoginMigrationParity.ps1 -ServerInstance TARGET -OutputFormat Csv -OutputPath .\output-files\migration\target-logins.csv

Then diff them with whatever you already use:

Compare-Object (Import-Csv .\output-files\migration\source-logins.csv) `
               (Import-Csv .\output-files\migration\target-logins.csv) `
               -Property login_name, sid_hex, is_disabled, default_database, default_language, password_hash_id

This script lives in the repo at:

• sql/migration/Get-LoginMigrationParity.sql
• powershell/wrappers/migration/Get-LoginMigrationParity.ps1

---

Example Output

One row per login, ordered by name, so a plain text diff is the whole comparison. Login names and
the domain below are illustrative; the measured source-versus-target comparison underneath them is
real output from a SQL Server 2025 instance.

login_name          login_type     is_disabled  default_db_state  default_language  check_policy  password_hash_id  server_roles
AppSupport_User     SQL_LOGIN      False        present           us_english        0             BDB2A33B9EF5
CONTOSO\dbateam     WINDOWS_GROUP  False        present           us_english        n/a           n/a               sysadmin
CONTOSO\svc_etl      WINDOWS_LOGIN  False        present           us_english        n/a           n/a
DBA_Junior          SQL_LOGIN      False        present           us_english        0             87307ADA3B82      sysadmin
OldContractor       SQL_LOGIN      True         present           us_english        0             E2C4C68BDF00      sysadmin
ReportingUser       SQL_LOGIN      False        MISSING           us_english        1             78CAAEE3A84A
sa                  SQL_LOGIN      False        present           us_english        1             1C0A58EEB04F      sysadmin

The case this was written for, measured

A throwaway login was set up the way a real one often is: disabled, in securityadmin, on the
British language, with password policy on. It was then migrated the naive way, recreated by name
with the same password typed in again. Here is what each check saw:

                   sid_hex                             is_disabled  language     policy  password_hash_id  roles
SOURCE             0x4E3C11409C4560468284478D71FFDA9F   True         British      True    B3856401B2FD      1
TARGET             0x948A5E6C96CF4F4B9671C50720AEB08D   False        us_english   True    2DF417F85CD9      1

count check        source 18  ->  target 18   PASSES, migration looks complete

The count check passes. Four attributes are wrong: a new SID that orphans every database user
mapped to it, a security account quietly re-enabled, a language change that alters date parsing,
and a password hash that proves the password was re-typed rather than carried across.

---

Understanding the Results

A row present on the source and absent from the target is the simplest finding of all: that login
did not migrate.

---

Best Practices

• Take the source fingerprint before the cutover, not after. Once the old server is
  decommissioned you have nothing left to compare against
• Keep both CSVs with the change record. They are small, they are plain text, and they are the
  evidence that the migration was complete
• Diff on login_name and sid_hex first. Everything else is a detail next to a SID mismatch
• Expect password_hash_id to read n/a for Windows logins. There is no stored hash, and that
  is correct rather than a gap
• Run it as a sysadmin, or accept a gap. Without CONTROL SERVER the hash column reads
  no-permission, and the script says so rather than quietly reporting a blank
• Do not stop at logins. Parity at the server level says nothing about database users. Pair it
  with Fix Orphaned Users

---

Related Scripts

You may also find these scripts useful:

• SQL Server Migration Script Generators (hub)
• Generate Login Script, the generator that produces the DDL this script then verifies
• Migration Login Audit and Post-Migration Validation, the count-level companion to this one
• Fix Orphaned Users, for when the SID comparison finds a mismatch
• Generate User Mapping Script
• Get Migration Risk Assessment
• Get Sysadmin Members
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

---

Summary

Counting logins proves nothing arrived missing. It cannot prove anything arrived correct. This
script produces one deterministic row per login covering the attributes a migration actually drops:
the SID, the disabled state, an explicit deny, the default database and language, the password
policy, a safe fingerprint of the stored hash, and server role membership. Run it on both servers,
diff the CSVs, and read sid_hex first.