How to collate sysname in sql server

Question

I am trying to run a query to drop server logins coming from windows and recreate them. I run this query to drop logins and it works fine

USE master
SELECT 
       'IF EXISTS (SELECT * FROM sys.server_principals WHERE name = ' + 
       '''' + [name] + '''' + ')' +
       ' BEGIN DROP LOGIN ' + QUOTENAME([name]) + ' END; ' + 
       'CREATE LOGIN ' + QUOTENAME([name]) + ' FROM WINDOWS; '
FROM sys.server_principals  
WHERE type_desc IN ('WINDOWS_GROUP' , 'WINDOWS_LOGIN')

But the following query

USE master
SELECT 
       'ALTER LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_DATABASE = ' + 
        spr.default_database_name
       + 'GRANT ' + spe.permission_name  + ' to ' + QUOTENAME([name])
       + CASE WHEN spr.default_database_name IS NOT NULL
            THEN ' WITH DEFAULT DATABASE = ' + spr.default_database_name
            ELSE 
            ''
        END
FROM sys.server_principals spr INNER JOIN 
     sys.server_permissions spe  ON spr.principal_id = spe.grantee_principal_id
WHERE spr.type_desc IN ('WINDOWS_GROUP', 'WINDOWS_LOGIN')

is returning this error

Cannot resolve collation conflict for column 1 in SELECT statement.

So when I investigated the issue it is coming from the sysname column default_database_name The query ran when I used spr.default_database_name COLLATE Latin1_General_CI_AI My three parts question is: Did I get the problem because of a row or because the column data type is sysname?. Is COLLATE Latin1_General_CI_AI the correct syntax for this scenario?. when I run this query

SELECT name, description
FROM sys.fn_helpcollations()

The results are showing many collations for different languages. So how did I get this error for a completely English database.

Edit: I ran these two queries to check the collation property for database and server

SELECT DATABASEPROPERTYEX(N'master', N'Collation')
SELECT SERVERPROPERTY(N'Collation')

and the result in both of them was

SQL_Latin1_General_CP1_CI_AS

Answer 1

It may help to bring the default collate for database and server instance, changing the query as shown below:

SELECT 
       'ALTER LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_DATABASE = ' + 
        spr.default_database_name COLLATE database_default
       + 'GRANT ' + spe.permission_name  + ' to ' + QUOTENAME([name])
       + CASE WHEN spr.default_database_name IS NOT NULL
            THEN ' WITH DEFAULT DATABASE = ' + spr.default_database_name COLLATE database_default
            ELSE 
            ''
        END
FROM sys.server_principals spr INNER JOIN 
     sys.server_permissions spe  ON spr.principal_id = spe.grantee_principal_id
WHERE spr.type_desc IN ('WINDOWS_GROUP', 'WINDOWS_LOGIN')

Answer 2

Solution for your question: Specify collation for two columns where you CONCAT

USE master
GO
SELECT 
   'ALTER LOGIN ' + QUOTENAME([name]) COLLATE SQL_Latin1_General_CP1_CI_AS + 
' WITH DEFAULT_DATABASE = ' + 
    spr.default_database_name COLLATE SQL_Latin1_General_CP1_CI_AS
   + 'GRANT ' + spe.permission_name  + ' to ' + QUOTENAME([name])
   + CASE WHEN spr.default_database_name IS NOT NULL
        THEN ' WITH DEFAULT DATABASE = ' + spr.default_database_name
        ELSE 
        ''
    END
FROM sys.server_principals spr INNER JOIN 
 sys.server_permissions spe  ON spr.principal_id = spe.grantee_principal_id
WHERE spr.type_desc IN ('WINDOWS_GROUP', 'WINDOWS_LOGIN')

Answer 3

It looks like someone at MS has fixed the collation on sys.server_permissions.state_desc as Latin1_General_CI_AS_KS_WS

I've tried on a number of instances (SQL 2016) and found the same problem. Putting "collate database_default" works.