I wrote this fairly self-contained STORED PROCEDURE forone of my websites to validate a user login. This STORED PROCEDURE has the following features:

* It returns TRUE if the login was successful
* It returns FALSE if the login failed
* It logs failed Logins
* It prohibits more than 7 failed login attempts ina 7 minute period.

What do you think? Email me with comments martin@martinwelch.net

CREATE PROCEDURE Member_ValidateLogin
(
@Username NVARCHAR(255),

@Password NVARCHAR(12),
@LoginIP NVARCHAR(255)

)

SET NOCOUNT ON

DECLARE @RETVAL INT
DECLARE @FailedLoginCount INT

--Check to see if this member has failed validation more the 7 times in 7 minutes

SET @FailedLoginCount = (SELECT COUNT(FailedLoginID) AS Expr1 FROM Members_FailedLogins WHERE (AttemptDate BETWEEN DATEADD(n, - 7, GETDATE()) AND GETDATE()))

IF @FailedLoginCount < 7
BEGIN

AS
--Check to see if a member record exists with the specified username/password combo

IF EXISTS(SELECT MemberID FROM Members WHERE Username = @Username AND Password = @Password)

BEGIN 
 --if it exists return MemberID 
 SET @RETVAL = (SELECT MemberID FROM Members WHERE Username = @Username AND Password = @Password)

END 

 ELSE

BEGIN

--if it does not exists return 0 and log it! 
 INSERT INTO Members_FailedLogins(Username, IPAddress)VALUES(@Username, @LoginIP)SET @RETVAL = 0
END 
 END
ELSE
   SET @RETVAL = 99 --Flag for too many failed logins
RETURN @RETVAL