Link to home
Start Free TrialLog in
Avatar of goodk
goodkFlag for United States of America

asked on

password and verify store procedures - not working -

After I exec and create a new user.
then I try to verify it but it is always returning "Failed"

am I doing it right?

----------

USE [SA02012011]
GO
/****** Object:  StoredProcedure [dbo].[pAddUser]    Script Date: 02/28/2011 10:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pAddUser](
      @UserName VARCHAR(20),
      @eMail VARCHAR(20),
      @Algorithm VARCHAR(16),
      @Pass VARCHAR(16)
)
AS
BEGIN
      set nocount on
      
      INSERT INTO PersonID (UserName, eMail, passwordID)
select @UserName, @eMail, hashbytes(@Algorithm,@Pass)
END


USE [SA02012011]
GO
/****** Object:  StoredProcedure [dbo].[pVerifyCredentials]    Script Date: 02/28/2011 10:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pVerifyCredentials](
      @UserName VARCHAR(20),
      @Algorithm VARCHAR(16),
      @Pass VARCHAR(16)
)
AS
BEGIN
      set nocount on
      select case num when 1 then 'OK'
                      else 'Failed'
                      end as Result
        from (
      SELECT count(*) as Num
        FROM PersonID
       WHERE UserName = @UserName
         AND passwordID = convert(binary(16),HASHBYTES(@Algorithm, @Pass))
              ) AS X
END
return

ASKER CERTIFIED SOLUTION
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

You need to compare password in the same way that you saved to database
Changed
passwordID = convert(binary(16),HASHBYTES(@Algorithm, @Pass))
to
passwordID = HASHBYTES(@Algorithm, @Pass)

Raj
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of goodk

ASKER

thanks - it was so timely!

and CORRECT