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]
(
@UserName varchar(50),
@Password varchar(50)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Err int
SELECT dbo.[User].Id
FROM dbo.[User]
WHERE (dbo.[User].Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS) AND (dbo.[User].Username = @UserName)
SET @Err = @@Error
RETURN @Err
END