Script for migrating server role permissions - SQL Server

Question

I need to obtain a script for all the user-defined server roles in my SQL Server instance with their assigned permissions, such as the below:

GRANT ALTER ANY AVAILABILITY GROUP TO [ServerRole1]

What I have done so far is:

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute 
FROM sys.server_principals SP 
  JOIN sys.server_permissions SPerm 
    ON SP.principal_id = SPerm.grantee_principal_id 
    where sp.type='R' and sp.name!='public'

but I am getting the error Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in add operator occurring in SELECT statement column 1.

I have then tried to solved it as below I am getting the error 'Expression type int is invalid for COLLATE clause':

SELECT ' GRANT ' + SPerm.permission_name + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute 
FROM sys.server_principals SP 
  JOIN sys.server_permissions SPerm 
    ON SP.principal_id collate SQL_Latin1_General_CP1_CI_AS  = SPerm.grantee_principal_id collate SQL_Latin1_General_CP1_CI_AS 
    where sp.type='R' and sp.name!='public'

Answer 1

In this case the issue was not with the join but with the text concatenation.

The following should work

SELECT ' GRANT ' + SPerm.permission_name COLLATE Latin1_General_CI_AS + ' TO '  + SP.name -- + ' '' ' AS Commands_To_Execute 
FROM sys.server_principals SP 
  JOIN sys.server_permissions SPerm 
    ON SP.principal_id = SPerm.grantee_principal_id 
    where sp.type='R' and sp.name!='public'

As an aside, I would strongly recommend looking at the Copy-DBAServerRole Powershell cmdlet in DBATools which is a Powershell module - it does the heavy lifting for you in jobs like these.