In SQL Server Authentication, when a login ID is added or a password is changed, the password encryption algorithm takes the supplied password and generates a numeric representation, which is stored in the syslogins table.
If the password is generated on a case-sensitive server, the original password text, as typed, is supplied to the password algorithm. When the password is subsequently checked, the user must supply the same characters in the appropriate case for authentication.
If the password is generated on a case-insensitive server, the password is converted to uppercase before generating and checking the encrypted form. When the password is subsequently checked, the user can supply the characters in upper-case or lowercase.
When changing sort orders, if a user transfers syslogins out of the server with one sort order and into a new server with another sort order, the encrypted passwords are moved as generated. This has the following effects:
When changing sort orders, take the appropriate steps to ensure that users are able to log in to the server when the sort order conversion is complete. If the system administrator is not able to log in because of the effects described, he or she must restore the original sort order to correct the problem.