sql-login-syncer: Copy SQL Server Logins Without Losing SIDs or Passwords
The Logins, SIDs, and Kerberos series spent three posts building up to a practical conclusion: SQL Server maps database users to logins by SID, not by name, and any login you recreate from scratch gets a new SID. Part 3 showed the cure for migrations: a CREATE LOGIN statement that carries the original password hash and the original SID, generated entirely from catalog views.
Running that generator by hand works fine for a one-time migration. It gets old quickly when you maintain Availability Group replicas or log shipping secondaries, where every new application login has to exist on every server in the topology with the same SID, or the next failover produces a crop of orphaned users.
So I turned the technique into a small open-source command-line tool: sql-login-syncer.
What It Does
Point it at a source instance and one or more targets:
|
1 |
sql-login-syncer --source PROD1 --target PROD2,PROD3 |
For every syncable login on the source that does not exist on a target, it generates and executes the same DDL pattern from part 3:
|
1 2 3 4 5 6 7 |
CREATE LOGIN [app_service] WITH PASSWORD = 0x0200173448CE3D98D9D3B05155623C92338AE9F32A8880605253E40FBD5B5E71BA153982C44955EA2D7175575DC9F3108F7A4FD904076677A5A56269588DD1F3046734BE6199 HASHED , SID = 0xABBA20EC21E90849B4690B758A654CDC , DEFAULT_DATABASE = [master] , DEFAULT_LANGUAGE = [us_english] , CHECK_POLICY = ON , CHECK_EXPIRATION = OFF; |
The password arrives as the salted hash SQL Server already stores, so the tool never sees a plaintext password, and the SID arrives byte-for-byte identical, so database users in restored or failed-over databases map correctly on the first try.
A few details it handles along the way:
- Windows logins and groups are created with
FROM WINDOWS. Their SIDs come from Active Directory (or the local SAM), so as part 1 explained, SQL Server resolves the same SID on every server in the domain without any help. - Disabled logins are created disabled on the target, so a decommissioned-but-retained account does not spring back to life on a secondary.
- Policy settings (
CHECK_POLICY,CHECK_EXPIRATION), default database, and default language are carried over. - Fixed and internal principals are excluded:
sa,NT SERVICE\...andNT AUTHORITY\...accounts, and##...##certificate-mapped logins stay put.
What It Refuses to Do
The tool never modifies a login that already exists on a target. No password overwrites, no SID surgery, no drops. If a login exists on both servers, it is skipped, with one important exception in the reporting:
|
1 2 3 |
WARNING: [app_service] already exists on PROD2 with a DIFFERENT SID (source 0x20FCA97943F58A4BB8E4565B3CFDF678, target 0x062E35FBDA0AC940870FDE860364E2C7). Database users mapped to this login will orphan on restore/failover. Skipping. |
That warning is the vanishing service account problem in miniature: same name, different identity. A login like this looks synced, and every database you restore from the source onto that target will arrive with an orphaned user. Finding these mismatches before a failover is arguably worth more than the syncing itself.
Previewing with –script-only
If you would rather review the DDL before anything touches a production server (a sentiment I endorse), --script-only prints the generated statements without executing them:
|
1 |
sql-login-syncer --source PROD1 --target PROD2 --login app_service --script-only |
You can also restrict a real sync to specific logins with --login, which takes a comma-separated list.
One caution: the scripted output contains password hashes. A hash is not a password, but a weak password’s hash can be cracked offline, so treat --script-only output with the same care as a backup file.
Permissions
Reading password hashes through LOGINPROPERTY(name, 'PasswordHash') requires CONTROL SERVER or membership in securityadmin on the source.[1] Without it, the function returns NULL and the tool reports an error for each SQL login rather than creating logins with wrong passwords. On each target you need ALTER ANY LOGIN. The tool uses Windows authentication only; there are no credentials to store or leak.
Where It Came From
The original version of this utility ran inside my server-maintenance framework and leaned on stored procedures in a central database to decide which servers were peers and to generate the DDL. This release genericizes it: the DDL generation moved into the tool itself (a query against sys.server_principals, sys.sql_logins, and LOGINPROPERTY[2]), and the target list became a command-line argument, so it runs against any SQL Server with no schema dependencies. It targets .NET 8 and was tested against SQL Server 2019 and SQL Server 2025.
The source, README, and MIT license are at code.hannahvernon.com/hannah-vernon/sql-login-syncer. Issues and pull requests are welcome.
If the SID mechanics underneath all of this are fuzzy, the series starts at part 1: what a SID actually is, and the migration technique the tool automates is walked through step-by-step in part 3.
References
- LOGINPROPERTY (Transact-SQL), Microsoft Learn. ↩
- sys.server_principals (Transact-SQL), Microsoft Learn. ↩