Logins, SIDs, and Kerberos from First Principles, Part 3: Migrating Logins Without Losing Them
Server migrations have a predictable failure mode: the databases restore perfectly, the application connection strings are updated, and then nothing can log in. Or worse, everything can log in but half the database users are orphaned. Both failures come from the same source: the new server’s logins were recreated from scratch, so they carry new SIDs (part 1 showed SQL login SIDs are random at creation), and for SQL logins, new passwords nobody knows.
The fix is to migrate the logins as they are: same SID, same password hash, same policy settings. SQL Server has supported this for a long time, and it takes exactly one carefully constructed CREATE LOGIN statement per login. This post builds that statement from catalog views, and by the end you will have the core of a complete migration generator. Demos ran against SQL Server 2019 (CU32) as the source and SQL Server 2025 (RTM) as the target.
Two Kinds of Login, Two Migration Problems
- Windows logins carry a SID issued by the domain. Any server in the same domain that runs
CREATE LOGIN [DOMAIN\account] FROM WINDOWSgets the identical SID from the domain controller, so Windows logins migrate correctly by accident, provided the account still exists and has not been recreated (part 2 covers that failure in detail). - SQL logins carry a SID invented by the source server and a password hash nobody can read back as cleartext. A plain
CREATE LOGIN ... WITH PASSWORD = 'something new'on the target produces a different SID and a different password: both halves of the identity are lost.
The migration problem is therefore a SQL login problem, and the solution has two parts: the SID = ... clause, and the PASSWORD = ... HASHED clause.[1]
The Source Login
A login on the source server, with a server role membership to make the migration interesting:
|
1 2 3 4 5 6 7 |
CREATE LOGIN [fleet_app] WITH PASSWORD = N'Correct!Horse#Battery9' , CHECK_POLICY = OFF , DEFAULT_DATABASE = [tempdb]; ALTER SERVER ROLE [bulkadmin] ADD MEMBER [fleet_app]; |
Generating the Portable CREATE LOGIN
Everything needed to reconstruct this login exists in the catalog. The SID is in sys.server_principals. The password hash comes from LOGINPROPERTY(name, 'PasswordHash').[2] The policy flags are in sys.sql_logins. Stitch them together:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT N'CREATE LOGIN ' + QUOTENAME([sp].[name]) + N' WITH PASSWORD = ' + [sys].[fn_varbintohexstr](CONVERT(varbinary(256), LOGINPROPERTY([sp].[name], 'PasswordHash'))) + N' HASHED' + N', SID = ' + [sys].[fn_varbintohexstr]([sp].[sid]) + N', DEFAULT_DATABASE = ' + QUOTENAME([sp].[default_database_name]) + N', CHECK_POLICY = ' + CASE [sl].[is_policy_checked] WHEN 1 THEN N'ON' ELSE N'OFF' END + N', CHECK_EXPIRATION = ' + CASE [sl].[is_expiration_checked] WHEN 1 THEN N'ON' ELSE N'OFF' END + N';' AS [cmd] FROM [sys].[server_principals] AS [sp] INNER JOIN [sys].[sql_logins] AS [sl] ON [sp].[sid] = [sl].[sid] WHERE [sp].[name] = N'fleet_app'; |
The output, straight from the source instance:
|
1 |
CREATE LOGIN [fleet_app] WITH PASSWORD = 0x0200dc9b846920ddbeb226f3d011e1efebc9536f860a87cb98ee1e1462fb1eb064e3d9eef5262c59a6705854cc0dd87fbaa7fd31f8e3ac4def834562c342f21f5d1eb0f4123c HASHED, SID = 0xf3c080946574e64fafa90c336776744b, DEFAULT_DATABASE = [tempdb], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF; |
Three things worth noticing:
- The
0x0200...prefix on the hash marks the algorithm version (SHA-512 with salt, used since SQL Server 2012). TheHASHEDkeyword tells the target to store this value as-is instead of hashing it again. - The
SID = 0xf3c0...clause pins the new login to the source SID. This is legal for SQL logins any time the SID is not already taken on the target. CHECK_POLICYandCHECK_EXPIRATIONtravel too. Recreating a login without them can accidentally subject an application password to expiration, and the resulting outage arrives weeks later with no obvious cause.
Replay on the Target
Run the generated statement on the target instance, then verify both halves of the identity survived. First the SID:
|
1 2 3 4 5 6 7 |
SELECT [name] , [sys].[fn_varbintohexstr]([sid]) AS [sid] FROM [sys].[server_principals] WHERE [name] = N'fleet_app'; |
|
1 2 3 |
name sid --------- ---------------------------------- fleet_app 0xf3c080946574e64fafa90c336776744b |
Identical to the source. Any database restored from the source maps its fleet_app user to this login with no orphan repair at all. Then the password, proven the only way that matters, by logging in with it:
|
1 2 3 |
> sqlcmd -S theTargetServer -U fleet_app -P "Correct!Horse#Battery9" -Q "SELECT SUSER_SNAME(), sys.fn_varbintohexstr(SUSER_SID());" fleet_app 0xf3c080946574e64fafa90c336776744b |
The original cleartext password, which never appeared anywhere in the migration, authenticates against the replayed hash. SID intact, password intact, zero orphans.
Scaling It Up
One login is a demo; a real migration handles all of them. Microsoft’s venerable sp_help_revlogin solved this same problem for decades,[3] and a production-grade generator wraps the query above in a cursor over sys.server_principals and adds:
- Windows logins: emit
CREATE LOGIN ... FROM WINDOWS, and validate each account against the domain first (xp_logininfothrows for deleted accounts, catching exactly the part 2 scenario before it ships to the new server). - Server role memberships: script
ALTER SERVER ROLE ... ADD MEMBERfromsys.server_role_members. - Disabled state: a disabled login on the source should arrive disabled on the target (
ALTER LOGIN ... DISABLE); silently re-enabling a login someone disabled for cause is a security regression. - Exclusions: skip
sa,NT SERVICE\...virtual accounts (their SIDs are machine-derived and recreate themselves), and certificate- or asymmetric-key-mapped logins, which need their key material migrated instead.
Explicit server-level and database-level permissions are a separate, larger problem: part 4 builds the permission-scripting half of the migration.
What to Take Away
- Never recreate SQL logins by hand during a migration. Fresh SIDs orphan every restored database user; fresh passwords break every application.
CREATE LOGIN ... HASHED, SID = ...preserves both halves of the identity. The generator query is a dozen lines against three catalog objects.- Carry the policy flags and disabled state along. They are part of the login’s security posture, not decoration.
- Windows logins migrate by SID automatically within a domain, but validate them against the directory first; the recreated-account trap is invisible to name-based scripts.
Next up: part 4 scripts out every permission a principal holds, at every scope, so the migrated logins can actually do their jobs.
Migration war stories? I would love to hear about it in the comments, or find me on Bluesky or LinkedIn.
References
- CREATE LOGIN (Transact-SQL) – Microsoft Learn. The HASHED and SID clauses used for portable login recreation. ↩
- LOGINPROPERTY (Transact-SQL) – Microsoft Learn. Retrieving the password hash and policy state for SQL logins. ↩
- Transfer logins and passwords between instances – Microsoft Support. The classic sp_help_revlogin approach this technique descends from. ↩