SQL Server: Create Login (Case Sensitive) Issue

I discovered an issue the other day when trying to add logins to a SQL Server with the collation of Latin1_General_BIN.

The T-SQL Command used was:

The error I was receiving was:
Windows NT user or group ‘DOMAIN\username’ not found. Check the name again.

However, the username was very much valid. What I then found if I changed the format to DOMAIN\Username (capital U) it began to work. However, on other servers (with same collation), it rejected the capital U and wanted a lowercase U. After doing some digging, it appears that at least with the Latin1_General_BIN collation (not sure about other collations, I would expect other case-sensitive collations would inhibit similar results) that Master remembers logins how they are created the first time for a specific server and must continue to be entered using the same case going forward. This appears to be true even if you delete the login and try to re-create it, it still wants the original case (Master appears to cache is indefinitely).

When working with logins on a Latin1_General_BIN SQL Server make sure to be consistent when typing the case for logins. If you can’t figure out what the correct case is supposed to be, it appears if you add the login with the GUI, it will auto change it to be the format that master remembers.

Leave a Reply

Your email address will not be published.