One of the challenges in managing SQL Server replicas is synchronizing logins between them. When setting up a secondary server, it is important to ensure that the logins on the primary server are also created on the secondary server. This ensures that users can access the databases on both servers seamlessly.

In response to a reader’s query, I have come up with a script that can be used to create multiple logins with their respective Security Identifiers (SIDs) and passwords. The script is as follows:

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%'

To use the script, you need to run it on the primary replica. After executing the script, you will get a result that contains the commands to create the logins. Simply copy the output and paste it into a query window on the secondary server. If you are using Windows logins, you need to create them on every instance. In the case of Windows logins, the SID is managed by Active Directory, so as long as the login exists on the primary replica, you will be able to access it on all replicas that are members of the availability group.

It is important to note that user information is part of the database and will be automatically synchronized through data synchronization.

I hope you find this script useful for synchronizing logins between SQL Server replicas. If you have any questions or suggestions, please feel free to reach out to me.