Azure SQL Managed Instance – Sync Agent Jobs and Logins in Failover Groups

Fantastic process and code - used it many times when I've had to sync.

Recently seen an issue when syncronizing Entra ID User accounts - the job runs without an error (returns code 0) but the generated SQL statement has syntax error and fails to run.

Here is my corrected code for the Creating DDL Statements for New Logins within the CreateLoginsInSecondary SP

        -- Insert DDL for new logins
        PRINT 'Creating DDL Statements for New Logins';
        INSERT INTO @DDLStmts 
        SELECT 
            CASE 
                WHEN l.[type] = 'S' THEN 
                    N' CREATE LOGIN [' + l.[name] + '] WITH PASSWORD=0x' 
                    + CONVERT(NVARCHAR(MAX), l.password_hash, 2) + N' HASHED'
                    + N', CHECK_POLICY = ' + CASE WHEN l.is_policy_checked = 1 THEN N'ON' ELSE N'OFF' END
                    + N', CHECK_EXPIRATION = ' + CASE WHEN l.is_expiration_checked = 1 THEN N'ON' ELSE N'OFF' END
                    + N', SID=0x' + CONVERT(NVARCHAR(MAX), l.[sid], 2)
					+ N', DEFAULT_DATABASE = [' + l.default_database_name + N']'
					+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
					+ N';'
                WHEN l.[type] IN ('E', 'X') THEN 
                    + N' CREATE LOGIN [' + l.[name] + '] FROM EXTERNAL PROVIDER'
					+ N' WITH DEFAULT_DATABASE = [' + l.default_database_name + N']'
					+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
					+ N';'
                WHEN l.[type] = 'R' THEN 
                    N' CREATE SERVER ROLE [' + l.[name] + ']'
					+ N', DEFAULT_DATABASE = [' + l.default_database_name + N']'
					+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
					+ N';'
            END 
        FROM @Logins l
        WHERE l.[name] NOT IN (SELECT [name] FROM master.sys.server_principals) 
        AND l.[type] IN ('S', 'E', 'X');

The problem was that FROM EXTERNAL PROVIDER requires the WITH clause for Default Database and Language instead of a comma.