SQL Stored Procedures To Run Automatically

Question

I have an asp.NET framework 4 web form website with user login capabilities etc etc... Im using visual studio 2010.

Each user has 3 login attempts once the log in attempts has passed 3, their account is locked. The only way to unlock these accounts is via the admin panel.

however a need a stored procedure or trigger that sets all the login attempts to zero each day. This way the login attempts wont be accumulative, just a daily counter.

How would this be done?

the login attempts are stored for each user in an Authentication table — AlexMorley-Finch, Sep 22 '11 at 08:57

Upendra Chaudhari · Answer 1 · 2011-09-22T09:08:04.153

1

If you storing login attempts count in any table for each user then you can create one job which runs every day and make attempt count to 0 for each user.

If you have no idea about how to create job then please refer this link :

Job in SQL 2005

Job in SQL 2000

edited Sep 22 '11 at 09:08

answered Sep 22 '11 at 08:55

Upendra Chaudhari

6,473
5
25
42

Sorry, I didn't make myself clear enough! How would i make the sql procedure run once every day? – AlexMorley-Finch Sep 22 '11 at 08:58
You not need to create any procedure. just create one job by following steps mentioned in link. Just change command like UPDATE Authentication SET attemptCount = 0. and schedule it for every day. – Upendra Chaudhari Sep 22 '11 at 09:01

score 0 · Answer 2 · answered Sep 22 '11 at 08:54

0

You can use the SQL Server Agent to schedule a job.

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

Add a step that simply sets the login attempts field to 0 for every user, and schedule it to run daily.

answered Sep 22 '11 at 08:54

AndrewC

6,680
13
43
71

score 0 · Answer 3 · edited Jan 18 '21 at 12:39

0

If you have control over your SQL Server, then have a look at running a SQL Agent Job or writing a Windows Service.

If you are outsourced to Azure, you can wrap your SProc in a Service call and use the Task Scheduler Service.

Also, look here and here

edited Jan 18 '21 at 12:39

Community

1
1

answered Sep 22 '11 at 09:01

StuartLC

104,537
17
209
285

score 0 · Answer 4 · answered Sep 22 '11 at 09:05

As the other answers say, you can create a job for SQL Server Agent, and have it execute once a day.

However, I generally distrust this kind of solution - if the Agent job fails for some reason, you could end up locking people out of the application and never know about it.

I'd recommend storing the attempts in a "login_attempts" table, with a time stamp, and writing your query to filter out attempts older than 24 hours.

SQL Stored Procedures To Run Automatically

4 Answers4