The SQL security login failed the error is below, any idear to fix it? (SQL 2008).
cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and SQL_Latin1_General_CP1_CI_AS in the UNIION operation. Error 468
Thanks,
Lynn
Microsoft SQL Server
Last Comment
Daniel_PL
8/22/2022 - Mon
8080_Diver
That is looking like a collation conflict in a query and not a login.
You can decide which collation you want as output and then cast every column and table from the "wrong" collation to the "right" collation. (It's a pain but it's even more fun to rebuild the database with a new collation and then sort out the bugs from other queries' JOINs. ;-)
I know but the same is in error message from situation I posted ;)
What pointed me to that is not exact quetion 'The SQL security login failed ...'
The best will be to hear from author from what he/she get's that error?
I will admit that the message opens with a statement that seemingly contradicts the contents of the error message.
yrcdba7
ASKER
This is error message when I click security login securibles.
cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and SQL_Latin1_General_CP1_CI_AS in the UNIION operation. Error 468
i recreaed login, so far user can access the server but the securibles still has this error. I agree it is table and database collation changed. I have to
change the collation for each of them. It is painful to change one by one table and database.
yrcdba7
ASKER
Daniel_PL
Isn't it an error prompted when viewing securables tab in login properties using SSMS?
Yes, but I don't know how to solve it from management studio.
It's a problem with different collations in master database and the database in which login is mapped as a db user.
You can check database collations by running:
EXEC sp_msforeachdb 'SELECT ''?'' AS [Database],DATABASEPROPERTYEX(''?'', ''Collation'') SQLCollation;'
I think you should change collactions so they can fit together.
Take care,
Daniel
Daniel_PL
Have it solved your problem?
As i wrote before, the problem lies in incompatible collations between master database and database in which login is mapped in.
Additionally it's not harmless to change master database collation - it implies rebuilding master db.
You can decide which collation you want as output and then cast every column and table from the "wrong" collation to the "right" collation. (It's a pain but it's even more fun to rebuild the database with a new collation and then sort out the bugs from other queries' JOINs. ;-)