User Login With Block Condition and Attempted Condition

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. 
  1.  
  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  
  8.       
  9. AS  
  10. BEGIN  
  11.   
  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'  
  23.   
  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  
  46.                           
  47.                                               else  
  48.                                                   begin  
  49.                                                         if (DATEDIFF(n,@logintime,getdate())<30)  
  50.                                                                set @counter=@counter+1  
  51.   
  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  
  64.                         
  65.           end   
  66.        -- print @succ     
  67.       
  68. END