Authenticate through CASE-Sensitive logins in Applications

Like for e.g.,

SELECT username FROM login where username = “abc”
SELECT username FROM login where username = “ABC”
SELECT username FROM login where username = “aBC”                    .. all returns same results.


Say, if you want to authenticate an user by name ‘Administrator’ (case-sensitive- ‘A’ CAPS) from a table say login

-- This query is of no use as it will return a result
SELECT username FROM login WHERE username = 'administrator'

There are many ways to accomplish case-sensitive comparison in SQL Server 2000


1.
-- Converting to Binary or Varbinary format returns that character’s ASCII value
-- which is not same for both the cases

-- this query will not return any records as ‘a’ is in smaller case
SELECT username FROM login WHERE cast(username as varbinary(10)) = cast ('administrator' as varbinary(12))
-- following query will be the true comparison
SELECT username FROM login WHERE cast(username as varbinary(10)) = cast ('Administrator' as varbinary(12))


2.
-- BINARY_CHECKSUM() function takes an input, and returns a checksum value for that input.
   -- this query will not return any records as ‘a’ is in smaller case
   SELECT username FROM login WHERE BINARY_CHECKSUM(username) =  BINARY_CHECKSUM('administrator')

   -- following query will be the true comparison
   SELECT username FROM login WHERE BINARY_CHECKSUM(username) =   BINARY_CHECKSUM('Administrator')

3.
-- We can use the COLLATE clause to specify a particular collation for an expression as shown below
SELECT username FROM login WHERE username = 'administrator' COLLATE SQL_Latin1_General_CP1_CS_AS


Additional Information:
----------------------------
-- We can use the following commands to check the current COLLATION property of the SQL Server.
SELECT SERVERPROPERTY('Collation') AS 'Server Level Collation'


-- OR
EXEC sp_helpsort

-- To view all the possible COLLATION properties available, use this command
SELECT * FROM ::fn_helpcollations()