How to script logins with the original password HASH and original SID

When migrating SQL Server instances, one of the most error-prone steps is transferring SQL logins to the destination. Simply scripting a login with CREATE LOGIN ... WITH PASSWORD won’t preserve the original password hash or the original SID — and mismatched SIDs between instance logins and database users will break application connectivity even if the password is correct.

Why SIDs Matter

Every SQL Server login has a SID (Security Identifier) stored at the instance level. Database users are mapped to logins via this SID. If you create a login on the destination with a new auto-generated SID, the database users inside your restored databases will be orphaned — they’ll exist but won’t be linked to any valid login. The fix is to script logins with their original SID and password hash from the source.

Quick Query: SID and Password Hash from the Source

Run this on the source instance as a member of the sysadmin role. Non-admin accounts will see incomplete or no output. The result is the raw binary SID and password hash — use the output to construct the CREATE LOGIN statement on the destination with the HASHED keyword.

SELECT
    name,
    CONVERT(VARCHAR(50), 'CREATE LOGIN [' + name + '] WITH PASSWORD=') AS stmt_prefix,
    CAST(password AS VARBINARY(26))                                     AS password_hash,
    'HASHED, SID='                                                      AS sid_prefix,
    CAST(sid AS VARBINARY(26))                                          AS sid
FROM sys.syslogins
WHERE password IS NOT NULL;

For a complete, production-ready login migration procedure that handles both Windows logins and SQL logins across different SQL Server versions (2005, 2008, 2012+), see the related article: Massive Database Migration: the complete procedure. That procedure includes the fn_hexadecimal helper function that converts binary values to the hex strings expected by CREATE LOGIN ... WITH PASSWORD = 0x... HASHED.

After migrating, run sp_change_users_login 'Report' on each restored database to identify any remaining orphaned users that need to be relinked.