I am getting the following error message when running the query below:
> Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation

This query works fine and basically gives me the scripts to CREATE USER in my current database.

In order to run the query in ALL (or a selection) of my databases in the current server, I have it done through dynamic SQL and for XML PATH.

The problem is,
when I uncomment the XML part of the script below, I get the error above.

It is not in ALL servers that I get this error. Some servers work fine, others are bogged.

I want this query to work in ALL servers, independently of server collations.

How can I achieve this?



    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    
    	DECLARE  @log NVARCHAR(MAX)
    			,@vCrlf CHAR(2);
    
    	SELECT  @log = ''
    		   ,@vCrlf = CHAR(13)+CHAR(10);
    
    
    
    SELECT @SQL = '
    --==========================================
    -- creating the users
    --==========================================
    
    select  db = db_name(),
             _login=[master].[sys].[server_principals].[name],
             role_= null,
    		 obj=null,
    		Permission= null,
    		[script]=
    ''CREATE USER [''       
     + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']'' 
             
    from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
    on [sys].[database_principals].[name]=[master].[sys].[server_principals].[name]
    where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')
    
    '
    
    --SET @SQL = (
    --			SELECT STUFF(
    --  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
    --								FROM sys.databases SD
    --								--INNER JOIN @DBS D ON SD.NAME = D.DB
    --								WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
    --								  AND SD.COMPATIBILITY_LEVEL > 80  
    
    --	  FOR XML PATH(''),TYPE)
    --  .value('text()[1]','nvarchar(max)'),1,2,N'')
    --)
    	
    
    EXECUTE MASTER.DBO.sp_executesql @SQL



**HERE IS THE WORKING VERSION** 




    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    
    	DECLARE  @log NVARCHAR(MAX)
    			,@vCrlf CHAR(2);
    
    	SELECT  @log = ''
    		   ,@vCrlf = CHAR(13)+CHAR(10);
    
    
    
    SELECT @SQL = '
    --==========================================
    -- creating the users
    --==========================================
    
    select  db = db_name(),
             _login=[master].[sys].[server_principals].[name],
             role_= null,
    		 obj=null,
    		Permission= null,
    		[script]= 
    ''CREATE USER [''       
     + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' COLLATE Latin1_General_CI_AS +  [master].[sys].[server_principals].[name] + '']'' 
             
    from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]  
    on [sys].[database_principals].[name] COLLATE Latin1_General_CI_AS = [master].[sys].[server_principals].[name]
    where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')
    
    '
    
    SET @SQL = (
    			SELECT STUFF(
      (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
    								FROM sys.databases SD
    								--INNER JOIN @DBS D ON SD.NAME = D.DB
    								WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
    								  AND SD.COMPATIBILITY_LEVEL > 80  
    
    	  FOR XML PATH(''),TYPE)
      .value('text()[1]','nvarchar(max)'),1,2,N'')
    )
    	
    
    EXECUTE MASTER.DBO.sp_executesql @SQL