how to user block if user enter five time wrong password and what is logic in SQL Server of block user for 30 minute if 5 time enter invalid password. and how to set this time 30 minute to 0 minute after 30 minute. I am trying in sql server
CREATE PROCEDURE Usp_CheckLoginDetail -- Add the parameters for the stored procedure here @Userid nvarchar(100), @Password varchar(500), @ResponseMsg int OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY IF EXISTS (SELECT Count(*) FROM TM_UserDetails WHERE UserId = @UserId) --Check User is Valid or not BEGIN IF EXISTS (SELECT Count(*) FROM TM_UserDetails WHERE UserId = @UserId AND [Password] = @Password and FirstLoginStatus=0 and FreezeStatus = 0 and BlockedStatus = 0)--Check User id and password is Valid BEGIN SELECT * FROM TM_UserDetails END ELSE if EXISTS(SELECT Count(*) FROM TM_UserDetails WHERE UserId = @UserId AND [Password] = @Password and FirstLoginStatus=1 and FreezeStatus = 1 and BlockedStatus = 0)--Check User id and password is Valid but user is doing first time login BEGIN SET @ResponseMsg = 2 -- First Time User --Code Will be write later END ELSE ---If User Enter Wrong Password code block BEGIN declare @Couter int = 1 declare @Count int declare @BlockCount int,@BlockedDte datetime,@BlockStatus bit select @BlockCount = ISNULL(BlockCount,0) , @BlockedDte = ISNULL(BlockedDate,getdate()) ,@BlockStatus = ISNULL(BlockedStatus,0) from TM_UserDetails where UserId = @Userid if @BlockCount < 5 begin set @BlockCount = @BlockCount + 1 update TM_UserDetails set BlockCount = @BlockCount , BlockedStatus = 1 , BlockedDate = GETDATE() where UserId = @Userid End else if @BlockCount = 5 begin SET @ResponseMsg = 5 -- Blocked User end END END ELSE BEGIN SET @ResponseMsg = 1 -- Invalid User END END TRY BEGIN CATCH SET @ResponseMsg = 3 -- Invalid User END CATCH END GO
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)