SQL Server Login Migration: What Gets Silently Left Behind

---

The Logins Arrived. That Is Not the Same as the Logins Migrating

Every SQL Server migration guide tells you to move the logins, and most of them point at
sp_help_revlogin, the Microsoft stored procedure that scripts out SQL logins with their password
hashes and SIDs preserved. It is a good procedure. It has been the standard answer for twenty
years, and the thing it does well, keeping the SID so your database users are not orphaned, is the
single most important part of the job.

What no guide tells you is the rest of the list. A login is not just a name, a password and a SID.
It carries a disabled flag, a default database, a default language, password policy settings,
server role memberships, and possibly an explicit denial of the right to connect at all. Some of
those transfer. Several do not. None of the ones that do not will produce an error, which is
why they are still wrong six months later.

This post is that list, and what to do about each one.

---

Why This Goes Wrong Quietly

The mechanism is the same every time and it is worth naming once, because it explains all of the
findings below.

CREATE LOGIN is not a snapshot restore. It is a statement with a fixed set of clauses, and it can
only carry what those clauses express. Everything about a login that lives outside those clauses,
a permission stored in another catalog view, a flag applied by a separate ALTER statement, is not
part of the statement and therefore is not part of the transfer.

Nothing errors, because nothing is wrong from SQL Server’s point of view. You asked it to create a
login. It created a login.

And the usual check afterwards is a count. Counting is the one measurement that survives every
mistake on this page: recreate all forty logins with fresh SIDs, every disabled one enabled and no
password policy, and the count is still forty.

---

What Comes Across, and What Does Not

The first thing to establish is which tool actually produced your login script, because they are not equivalent and the differences are where the damage is.

Microsoft's sp_help_revlogin is better than its reputation. Reading the source, each login it emits is built as:

CREATE LOGIN [name] WITH PASSWORD = 0x0200... HASHED, SID = 0x29674..., DEFAULT_DATABASE = [master]
    , CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
DENY CONNECT SQL TO [name];        -- only when the source had it denied
ALTER LOGIN [name] DISABLE;        -- only when the source login was disabled

So the disabled flag and an explicit connect denial do come across, provided that is the procedure you ran.

So where does the damage actually come from?

From the fact that most teams are not running that procedure. They are running a hand-rolled script somebody wrote years ago, an SSMS Script Login as CREATE To (which emits no password and no SID at all), or a generator from a toolkit. Those are the ones that quietly drop things, and the only way to know is to read what yours emits.

A worked example, and it is mine. The generator in my own repo had two defects until this was written. It never emitted ALTER LOGIN ... DISABLE, so a disabled account arrived enabled with its original password still valid.

And its Windows-login section filtered on WHERE type IN ('W','G') when there is no type 'W' in sys.server_principals, so it silently produced no Windows logins on any server it had ever run against, while the role-membership section further down still emitted ALTER SERVER ROLE ... ADD MEMBER for those same logins behind an IF EXISTS guard that was false on the target. Both fixed, both found by running it and reading the output rather than reading the code.

That is the actual lesson of this post. The question is not “does a login transfer lose things”, it is “what does the specific script I am about to run emit, and have I read it?”

---

The Three That Bite Hardest

1. A disabled login comes back enabled (when your script forgets it)

This is the one worth checking first, because it is a security regression rather than an inconvenience, and it is completely invisible. sp_help_revlogin handles it. Plenty of other scripts, including one of mine, did not.

There is no CREATE LOGIN ... DISABLED. The state has to be re-applied as a second statement:

-- What a complete transfer looks like for a login that was disabled on the source
CREATE LOGIN [OldContractor]
    WITH PASSWORD = 0x0200A1B2... HASHED,
         SID      = 0x296749E9ED4D3E43BCF3A956B43ED1E6,
         DEFAULT_DATABASE = [master],
         DEFAULT_LANGUAGE = [British],
         CHECK_POLICY     = ON,
         CHECK_EXPIRATION = OFF;

-- Without this line the account is live on the new server, with the old password still valid
ALTER LOGIN [OldContractor] DISABLE;

Why it matters more than it sounds: the login you disabled is, by definition, one somebody decided
should not be usable. Contractors who left. Service accounts for a decommissioned application. An
old sa-equivalent kept for break-glass. Those are exactly the accounts you least want quietly
re-armed on a fresh server, and the SID and password came across perfectly, so the old credentials
work on the first try.

Worse, most transfer scripts also carry role membership as a separate step, and that step usually
does work. So the account can be re-enabled and re-granted sysadmin in the same run.

To find them before you migrate:

SELECT name, type_desc, is_disabled, create_date, modify_date
FROM   sys.server_principals
WHERE  is_disabled = 1
  AND  type IN ('S', 'U', 'G')
ORDER BY name;

Keep that list. After the migration, run it on the target and confirm the same names come back.

2. A default database that does not exist yet

DEFAULT_DATABASE is scripted as a name, and names resolve at connection time rather than at
creation time. So a login pointing at a database you have not restored yet is created without
complaint, and then refuses every connection until that database appears.

The symptom is misleading. The user reports “I cannot log in”, the error mentions the login, and
everybody spends an hour on authentication when the login and the password are both fine.

-- Logins whose default database is missing on THIS server
SELECT p.name, p.default_database_name
FROM   sys.server_principals AS p
WHERE  p.type IN ('S', 'U', 'G')
  AND  p.default_database_name IS NOT NULL
  AND  DB_ID(p.default_database_name) IS NULL
ORDER BY p.name;

Run that on the target after the restores. It should return nothing. If it returns rows, either the
database is still to come or the login should be repointed:

ALTER LOGIN [ReportingUser] WITH DEFAULT_DATABASE = [master];

3. A deny that is not a disable

is_disabled and DENY CONNECT SQL look identical from the outside. The user cannot connect
either way. Internally they are unrelated: one is a flag on the principal, the other is a permission
row, and a script that transfers principals carries the first and never the second.

-- Explicit denials at server scope, which no CREATE LOGIN will reproduce
SELECT pr.name, perm.permission_name, perm.state_desc
FROM   sys.server_permissions AS perm
JOIN   sys.server_principals  AS pr ON pr.principal_id = perm.grantee_principal_id
WHERE  perm.state = 'D'
ORDER BY pr.name, perm.permission_name;

Anything this returns on the source needs re-applying by hand on the target.

---

The Things You Simply Cannot Script

Worth stating plainly, because a checklist that pretends to be complete is worse than one that
admits its edges:

• Credential secrets. A login mapped to a credential loses the mapping, and the password inside
  the credential is not readable from the source at any permission level. Somebody has to re-enter
  it from wherever your organisation actually keeps it
• Linked server remote passwords. Same situation. A linked server login mapping that uses stored
  credentials can be scripted right up to the password and no further
• Contained database users with their own password. These live inside the database rather than
  at server scope, so a login transfer never sees them at all, and the password cannot be read.
  They are also easy to script wrongly in a way that succeeds, which is its own article
• Anything Active Directory owns. A Windows login is a pointer to an AD principal. If the target
  server is in a different domain or has no trust, scripting the login perfectly still gives you an
  account that cannot authenticate

The right response to all four is the same: produce the list before the cutover, and put the manual
steps in the runbook rather than hoping they surface.

---

A Practical Order of Work

1. Before the window, on the source: capture the disabled list, the deny list, the credential
   and proxy list, and a full login fingerprint. These take minutes and cannot be recovered once
   the old server is gone
2. Generate the login script with SIDs and hashes preserved, and read it. Specifically, check
   that the disabled accounts have an ALTER LOGIN ... DISABLE line and that the Windows section
   is not empty
3. Restore the databases first, then create the logins, so default databases resolve
4. Re-apply what could not be scripted: denies, credentials, proxies, linked server passwords
5. Compare, do not count. Diff the login fingerprints from both servers. Read the SID column
   first, then the disabled column
6. Fix orphaned users for anything the SID comparison flags

Steps 1 and 5 are the same script run twice, which is the point of
Get Login Migration Parity. Step 2 is
Generate Login Script. Step 6 is
Fix Orphaned Users.

---

Frequently Asked Questions

---

Summary

Moving logins is treated as a solved problem because the hardest part of it, preserving the SID,
was solved a long time ago. The rest of the login was never in scope for that solution, and nothing
in SQL Server will tell you so. A disabled account comes back enabled. A deny does not travel at
all. A default database resolves to nothing and presents as an authentication failure. Credentials
and contained users cannot be scripted at any permission level.

None of those raise an error, and a count check passes every one of them. Capture a full fingerprint
from the source before the cutover, take the same one from the target afterwards, and diff the two.
Compare, do not count.