String in SQL Server are by default case insensitive. That means that “SQL” is equivalent to “sql” and it causes problems when you’re making a password authentication mechanism which is supposed to be case senstive. Here’s an example stored procedure in which I’m making a case sensitive string comparison for the password field using Collations.
CREATE PROCEDURE [dbo].[proc_AuthenticateUser]
SET NOCOUNT ON
DECLARE @Err int
WHERE (dbo.[User].Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS) AND (dbo.[User].Username = @UserName)
SET @Err = @@Error