To replicate SQL Server logins from one server to another: including their SIDs (Security Identifiers), you need a script that extracts login details from the source server and generates CREATE LOGIN statements for the target server. This ensures that database users remain mapped correctly, avoiding orphaned users.

Here’s a full script to do just that:

SQL Script: Generate CREATE LOGIN Statements with SIDs

Run this on the source server:

-- Create script to replicate logins with SID and hashed password
SELECT
    'CREATE LOGIN [' + sp.name + '] ' +
    CASE
        WHEN sp.type_desc = 'SQL_LOGIN' THEN
            'WITH PASSWORD = ' + CONVERT(VARCHAR(MAX), sl.password_hash, 1) + ' HASHED, ' +
            'SID = ' + CONVERT(VARCHAR(MAX), sp.sid, 1) + ', CHECK_POLICY = ' +
            CASE WHEN sl.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_EXPIRATION = ' +
            CASE WHEN sl.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END
        WHEN sp.type_desc = 'WINDOWS_LOGIN' THEN
            'FROM WINDOWS'
        ELSE ''
    END + ';' AS [CreateLoginScript]
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
WHERE sp.type IN ('S', 'U', 'G') -- SQL logins, Windows logins and Windows groups
  AND sp.name NOT LIKE '##%' -- Exclude system logins
  AND sp.name NOT IN ('sa'); -- Optional: exclude 'sa'

Output Example

CREATE LOGIN [AppUser] WITH PASSWORD = 0x0100... HASHED, SID = 0x5F3A..., CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
CREATE LOGIN [DOMAIN\User1] FROM WINDOWS;

You can copy the output and run it on the target server to recreate the logins with the same SIDs and password hashes.

Notes

• Windows logins and groups take their SID from Active Directory, so they’re created with FROM WINDOWS and no SID. (Specifying a SID for a Windows login is an error.)
• Default database, server role membership, permissions and disabled status aren’t copied. Microsoft’s sp_help_revlogin covers more of this.
• This script does not reveal actual passwords, only their hashes.
• You must run it on a server where you have sysadmin privileges.
• If you’re migrating users between servers that host Availability Groups, preserving SIDs is essential to avoid orphaned users.

From my SQL Server DBA handbook.