Did you know that in SQL Server 2000, the engine used to maintain two copies/versions of each SQL Server login password. One contained the actual password supplied by the user, and the second one was the password converted by SQL Server to all uppercase letters.
This was done to speed up case-insensitive validation of passwords, as SQL 2000 allowed users to login using passwords in uppercase, lowercase (or combination). Although this behavior is convenient for many users, it makes password-guessing attacks easier by reducing the number of possible passwords.
With SQL Server 2005, SQL Server stores only one and original copy of the password. A password entered by a user must match the password stored on the server. If the password does not match the password stored on SQL Server, the login fails and user is denied access to SQL Server.
If the precise case of the password characters is forgotten, the password must be reset by using
ALTER LOGIN LoginName WITH PASSWORD = 'NewPassword';
Special thanks to Saleem Hakani for this post!