User Name, Password Case sensetive

msbsam · September 29th, 2007, 05:58 AM

hi all,
I have big problem to check login user username and password case sensetive.
im doing asp.net(c#) website using sqlserver 2000 database. I tried to check user name casesensitive. But it isnt check and only checking all word. If My User name and database finding record is true then user can log to sytem. But i want to check also UserName is casesensitive or not.
Can anybody help me to do this thing...
Thank you

joefawcett · September 29th, 2007, 09:00 AM

If you are testing within a stored procedure then you can use a case-sensitive collation, this fragment is from one I wrote where @Password is what was entered and @CurrentPassword is what is held in the table:

Code:

IF @CurrentPassword = @Password COLLATE SQL_Latin1_General_CP1_CS_AS RETURN 0;
  RETURN 1;

In this stored procedure 0 returned means valid, 1 means failed.

If you are testing within your C# code then you can use string.Compare but pass false for the third parameter, ignoreCase.

--

Joe (Microsoft MVP - XML)

msbsam · October 1st, 2007, 01:21 AM

Hello joefawcett,

Can understand what you said. But i tried to apply it. Cant do it to me. This is my SP, Please help me to modify it joefawcett.

CREATE PROCEDURE [dbo].[SEARCH_USERLOGING_STATUS]
    @Usr_Name varchar(100),
    @Usr_Password varchar(50),
    @Usr_Macaddress varchar(50)
AS

SELECT USR_ID, USR_BRANCH_CODE, USR_NAME, USR_PASSWORD, USR_MAC_ADDRESS, USR_HIERARCHY_LEVEL,USR_IS_ADMIN,USR_IS_SUPPER
FROM Users
WHERE (Usr_Name = @Usr_Name) AND (Usr_Password = @Usr_Password) AND(USR_MAC_ADDRESS=@Usr_Macaddress)
GO

Thanx

joefawcett · October 1st, 2007, 08:56 AM

What did you try?

Code:

CREATE PROCEDURE [dbo].[SEARCH_USERLOGING_STATUS] 
    @Usr_Name varchar(100),
    @Usr_Password varchar(50),
    @Usr_Macaddress varchar(50)
AS

SELECT    USR_ID, USR_BRANCH_CODE, USR_NAME, USR_PASSWORD, USR_MAC_ADDRESS, USR_HIERARCHY_LEVEL,USR_IS_ADMIN,USR_IS_SUPPER
FROM       Users
WHERE     (Usr_Name = @Usr_Name COLLATE SQL_Latin1_General_CP1_CS_AS) AND (Usr_Password = @Usr_Password COLLATE SQL_Latin1_General_CP1_CS_AS) AND(USR_MAC_ADDRESS=@Usr_Macaddress)
GO

--

Joe (Microsoft MVP - XML)

msbsam · October 3rd, 2007, 12:17 AM

thank you so much joefawcett.

MSB