Create Identical Login on Another Server With The Same SID

From Relyimah

For mirroring especsially, it is sometimes necessary to have an identical login on two different SQL Server Instances. This will also allow you to restore a database backup from one server, and apply it to the other server without having to reset the user logins.

To achieve this, run the following command on the server you wish to create the identical logins from:

SQL Query:

SELECT 
'create login [' + p.name + '] ' + 
case when p.type IN('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON  l.credential_id = c.credential_id
WHERE p.type IN('S','U','G')
AND p.name <> 'sa';

This will generate a list of all logins on the server (except for 'sa'). From this, yo can choose to either generate all the logins on the other server, or just one. To use the generated commands, you will need to drop any existing logins with the same ID, and reset the user logins afterwards.