Copy Database Login User Account After Database Restore

Note:

• Run the following on the source server and run the output on the destination server.
• Run after database has been restored.
• Apply to restored databases from PROD backup.
• Apply to AlwaysOn databases.

SELECT 'create login [' + sp.name + '] ' + CASE
        WHEN sp.type IN (
                'U'
                ,'G'
                )
            THEN 'from windows '
        ELSE ''
        END + 'with ' + CASE
        WHEN sp.type = 'S'
            THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' + CASE
                    WHEN sl.is_expiration_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + 'check_policy = ' + CASE
                    WHEN sl.is_policy_checked > 0
                        THEN 'ON, '
                    ELSE 'OFF, '
                    END + CASE
                    WHEN sl.credential_id > 0
                        THEN 'credential = ' + c.name + ', '
                    ELSE ''
                    END
        ELSE ''
        END + 'default_database = ' + sp.default_database_name + CASE
        WHEN len(sp.default_language_name) > 0
            THEN ', default_language = ' + sp.default_language_name
        ELSE ''
        END
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id
WHERE sp.type IN (
        'S'
        ,'U'
        ,'G'
        )
    AND sp.name <> 'sa'
    AND sp.name NOT LIKE 'NT Authority%'
    AND sp.name NOT LIKE 'NT Service%'