0

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

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.

  • It worked, thanks a lot! However, can you explain why please? – user1930901 Feb 19 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.