xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation
When executing the following in a database having a default collation that is different than the instance-level collation:
exec xp_logininfo N'NT AUTHORITY\SYSTEM';
you will get the following error:
Msg 468, Level 16, State 9, Procedure xplogininfo, Line 107 [Batch Start Line 0]
Cannot resolve the collation conflict between "Hebrew100CIASSC" and "Tamazight100_BIN2" in the equal to operation.
That error is coming from the following predicate in the final query in that stored procedure:
where l.loginname = n.name
Using the following:
EXEC sphelptext N'sys.xplogininfo';
we can see that the problem originates in the creation of the local temp table:
CREATE TABLE #nt (name sysname collate catalog_default, sid varbinary(85), sidtype int)
The issue is the "collate catalogdefault" clause. This was probably added to fix a collation mismatch error that would happen in partially contained databases since the "loginname" column comes from "syslogins" which is instance-level meta data that uses CATALOGDEFAULT in partially contained DBs, whereas the temp table default collation uses DATABASE_DEFAULT in partially contained DBs. (here is the Partially Contained DB collation documentation: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations ).
This solution fixes that problem, but creates a new problem when using non-contained DBs since "syslogins" will be using the instance-level collation yet the temp table will now be using the default collation of the current DB since that is what CATALOG_DEFAULT maps to for non-contained DBs. When the instance-level and database-level collations match, there is no error. But when those two collations do not match, this becomes a case where not having the COLLATE clause in the CREATE temp table statement would have been better.
There are three ways to fix this, and both include keeping the COLLATE clause in the CREATE temp table statement:
1) do not specify the COLLATE clause in the CREATE table statement. Instead, specify the COLLATE clause on that predicate in the query. Of course, picking a case-insensitive collation for a sytem that is case-sensitive/binary and has logins that vary only in casing, that would be a problem.
2) do not specify the COLLATE clause in the CREATE table statement. Instead, create a conditional branch and test to see if the current DB is partially contained. Execute a different query depending on the result of that test. The problem here is that the overall problem is in both of the final 2 queries, so the conditional branch would need to be duplicated.
3) Implement the following suggestion to create an INSTANCEDEFAULT collation keyword that would match the "tempDB collation" when in a non-contained DB, but would match CATALOGDEFAULT when executed in a partially-contained DB ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36197932-add-special-collation-instance-default-to-work-lik ). This would allow for using "COLLATE INSTANCE_DEFAULT" in the CREATE temp table statement.
While option #3 is the most ideal and reliable, the following query shows option #1 in action (because it is easy and can be done immediately):
DECLARE @acctname sysname;
declare @type varchar(8)
select TOP 1
'account name' = @acctname,
'type' = @type,
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = @acctname,
'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
where l.loginname = n.name COLLATE CATALOG_DEFAULT and hasaccess = 1
order by 3, 5
-- no error due to COLLATE on "l.loginname = n.name" predicate
Solomon Rutzky commented
Actually, I am not sure why the "collate catalog_default" clause was added to the CREATE temp table statement. I had thought that it was to fix potential issues when used within Contained databases, but this can't be since:
a) the instance-level meta-data (i.e. syslogins.loginname) is still in the original instance-level collation (nothing changed there for Contained DBs ; it's _not_ CATALOG_DEFAULT) so this system stored proc only works within Contained DBs _if_ the instance-level collation is Latin1_General_100_CI_AS_KS_WS_SC (which is what CATALOG_DEFAULT equates to within Contained DBs), and
b) Windows Logins and Groups that are direct to a Contained DB (without having a Login defined mapped in SQL Server) are actually Users, not Logins, so there is nothing to return from this proc even if the instance-level collation is Latin1_General_100_CI_AS_KS_WS_SC (yes, I verified this in a Contained DB on an instance using Latin1_General_100_CI_AS_KS_WS_SC as the instance-level collation).
All that being said, implementing my suggestion of an INSTANCE_DEFAULT pseudo-collation (option #3 in the main post), and using that in the CREATE temp table statement, is still the best, most reliable, most flexible solution for this problem and any others dealing with instance-level object names (or non-name strings, but I think most use-cases deal with names).