How to make a case sensitive comparision of strings in SQL Server

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