Background

In our simple life we have many accounts, like bank account, insurance account, Facebook account, Gmail account and so on. I forget my password regularly but thank you developers for providiing me a password recovery capability. Without a password recovery capability I would have no way to remember my password. In this article I have a single table tbl_ALogin.
  1. select * from tbl_ALogin
  1. LoginID UserName Password Salt LoginDateTime LastLoginTime IsBlocked AccessCounter
  2. 1 abc 74e9f6722b6df40630d8d7249078453ad1af05c8 a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0 0
  3. 5 cde 62ee78aa3cb90bde955166e6478d4f807f6dea0c 90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0 0
We can see the preceding table has two columns, one is "IsBlocked" and the other is "AccessCounter". These columns provide access and not access permission.
I assume cde user name has the password admin123.
For example, now enter the wrong password.
The following shows what has changed in the table after the wrong password.
  1. LoginID UserName Password Salt LoginDateTime LastLoginTime IsBlocked AccessCounter
  2. 1 abc 74e9f6722b6df40630d8d7249078453ad1af05c8 a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0 0
  3. 5 cde 62ee78aa3cb90bde955166e6478d4f807f6dea0c 90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0 1
Where is the check in Stored Procedure access counter:
  1. if (DATEDIFF(n,@logintime,getdate())<30)
  2. set @counter=@counter+1
If we enter the wrong password again then the access counter is incremented.
  1. LoginID UserName Password Salt LoginDateTime LastLoginTime IsBlocked AccessCounter
  2. 1 abc 74e9f6722b6df40630d8d7249078453ad1af05c8 a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0 0
  3. 5 cde 62ee78aa3cb90bde955166e6478d4f807f6dea0c 90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 0 2
If we enter the wrong password then again our account is blocked as in the following:
  1. if(@counter>2)
  2. begin
  3. update tbl_ALogin set isblocked=1,AccessCounter=@counter,Logindatetime=getdate() where LoginID=@tmpuserid
  4. set @Succ=-1
  5. end
After the blocked account the following shows how it is displayed:
  1. LoginID UserName Password Salt LoginDateTime LastLoginTime IsBlocked AccessCounter
  2. 1 abc 74e9f6722b6df40630d8d7249078453ad1af05c8 a8947f78d0ca294c 2012-11-14 10:03:53.070 2012-11-14 10:03:53.070 0 0
  3. 5 cde 62ee78aa3cb90bde955166e6478d4f807f6dea0c 90f62957f3e9bab 2014-12-20 12:46:46.610 2012-11-14 13:34:18.623 1 3
The following is the complete procedure of the user login.
  2. ALTER PROCEDURE [dbo].[proc_UserLogin]
  3. -- Add the parameters for the stored procedure here
  4. @username varchar(100),
  5. @password varchar(50),
  6. @succ int out,
  7. @LoginID int out
  9. AS
  10. BEGIN
  12. -- SET NOCOUNT ON added to prevent extra result sets from
  13. -- interfering with SELECT statements.
  14. SET NOCOUNT ON;
  15. declare @tmpuserid int
  16. declare @isblocked bit
  17. declare @logintime datetime
  18. declare @validpassword varchar(50)
  19. declare @counter int
  20. -- Insert statements for procedure here
  21. select @tmpuserid=LoginID,@validpassword=Password,@isblocked=isblocked,@logintime=Logindatetime,@counter=isnull(AccessCounter,0) from tbl_AdminLogin where username=@Username
  22. --select @tmpuserid=LoginID,@validpassword=Password,@isblocked=isblocked,@logintime=Logindatetime,@counter=isnull(AccessCounter,0) from tbl_AdminLogin where username='admin'
  24. -- print DATEDIFF(n,@logintime,getdate())
  25. if (@isblocked=1 and (DATEDIFF(n,@logintime,getdate())>30) and @validpassword=@Password)
  26. begin
  27. update tbl_AdminLogin set isblocked=0,Logindatetime=getdate(),AccessCounter=0 where LoginID=@tmpuserid
  28. set @succ=1
  29. set @LoginID=@tmpuserid
  30. end
  31. else
  32. begin
  33. if (@isblocked=1 and @validpassword<>@Password)
  34. set @Succ=-1
  35. else
  36. if(@isblocked=1)
  37. set @Succ=-1
  38. else
  39. begin
  40. if (@isblocked=0 and @validpassword=@Password)
  41. begin
  42. update tbl_AdminLogin set isblocked=0,Logindatetime=getdate(),AccessCounter=0 where LoginID=@tmpuserid
  43. set @succ=1
  44. set @LoginID=@tmpuserid
  45. end
  47. else
  48. begin
  49. if (DATEDIFF(n,@logintime,getdate())<30)
  50. set @counter=@counter+1
  52. if(@counter>2)
  53. begin
  54. update tbl_AdminLogin set isblocked=1,AccessCounter=@counter,Logindatetime=getdate() where LoginID=@tmpuserid
  55. set @Succ=-1
  56. end
  57. else
  58. begin
  59. update tbl_AdminLogin set AccessCounter=@counter where LoginID=@tmpuserid
  60. set @succ=-2
  61. end
  62. end
  63. end
  65. end
  66. -- print @succ
  68. END