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'