enable user after 30 min when thy get disabled by entering wrong password 3 times

Posted by Rickeybglr under Sql Server on 7/18/2012 | Points: 10 | Views : 1851 | Status : [Member] | Replies : 5
Hi,
wat i am trying to do is:
when user enter wrong password 3 times his flag changed from 1 to 0 in registration table. -- its done
login SP:
ALTER PROCEDURE [dbo].[USP_UserLogin]
(
@UserName varchar(50),
@Password VARCHAR (80)
)
AS
BEGIN
BEGIN TRY
DECLARE @USERID INT=0
select @USERID=userid from userRegistrationTB where username=@username
IF EXISTS(SELECT userID from userRegistrationTB
WHERE username=@UserName AND password=@Password COLLATE SQL_Latin1_General_CP1_CS_AS AND isActive=1)
BEGIN
SELECT '0|Login successfully' AS result
END
ELSE
BEGIN
SELECT '1|Login Fail' AS result
exec USP_BlockUser @USERID,@password
END
END TRY
BEGIN CATCH
SELECT '3|Table not found' As result
END CATCH
END


-----
ALTER PROCEDURE [dbo].[USP_BlockUser]
(
@pswd nvarchar(30),
@userID INT )
AS
BEGIN
-- DECLARE @ID int=0
INSERT INTO userLoginStatusTB
(
wrongAttempt,
isactive,
UserID
)
VALUES
(
0,
0,
@userID
)
Declare @tempAttempt int=0
SELECT @tempAttempt= wrongAttempt from userLoginStatusTB where UserID=@userID
if(@tempAttempt=0)
BEGIN
update userLoginStatusTB set wrongAttempt=1 WHERE UserID=@userID
END
ELSE IF (@tempAttempt=1)
BEGIN
update userLoginStatusTB set wrongAttempt=2 WHERE UserID=@userID
END
ELSE IF(@tempAttempt=2)
BEGIN
update userLoginStatusTB set wrongAttempt=3 WHERE UserID=@userID
END
ELSE
BEGIN
UPDATE userRegistrationTB SET isActive=0 WHERE username=@userID
END
SET NOCOUNT ON;
END
--- i have another table which has user login info. and wrong attempt column when its value become 3 it will update registration table and set flag=0
so user wont be able to login.

wat i want:
after 30 min. this isactive flag will automatically set to 1 and in userLoginStatusTB wrongattempt flat become 0 so that user can again login




Responses

Posted by: Ranjeet_8 on: 7/18/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Create a table that contain the user_id and LockedTime.
check with the LockTime is greater then 30 min then u can allow the user to login.

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rickeybglr on: 7/18/2012 [Member] Starter | Points: 25

Up
0
Down
but i want this to be done automatically

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dotnetrajanikanth on: 7/18/2012 [Member] Starter | Points: 25

Up
0
Down
Rickeybglr,

perhaps you need a scheduler for doing this.

Windows Task Scheduler may do the job.

Use of a scheduler is actually a high cost process. it may use a lot of memory.

so its better to do like this

Instead of automatically activating the user you can activate the user during the next login after 30 mins (if locktime less than current time activate the user as told in the previous post)

____________
www.flickr.com/photos/psdesigner/

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 7/18/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
u can do the same using SQL Jobs Scheduling.
after 30 min u can unlock the user.

how to create Sql jobs scheduling. (Refer this url)
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 7/18/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
One more link for job scheduling

http://msdn.microsoft.com/en-us/library/ms187910.aspx

.

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response