这个储存过程可以帮你解决问题。create PROCEDURE spCheckUserPsw ( --检测用户(存在的)密码是否正确, 并维护错误次数等 @gdUserGuid uniqueidentifier, --用户GUID(BAS_LoginUser) @chvPsw varchar(100), --密码 @intResult INT OUTPUT, --处理结果 0 正确 1 错误 @chvErrorMsg NVARCHAR(1000) OUTPUT --错误信息 ) AS DECLARE @intMaxLoginErrorCount INT DECLARE @intACCountLockTime INT DECLARE @intMaxListNo INT DECLARE @intCount INT DECLARE @intLogErrorCount INT DECLARE @dtLockTime Datetime DECLARE @chvPassword varchar(100) DECLARE @gdAdminGuid uniqueidentifier DECLARE @chvAdminPsw varchar(100) DECLARE @chvUserID VARCHAR(20) BEGIN SET @intMaxLoginErrorCount = CONVERT(INT, dbo.fxGetSystemParameterValue('MaxLoginErrorCount')) --** 改变代码位置,需要用到时,再读取这个值 SET @intACCountLockTime = CONVERT(INT, dbo.fxGetSystemParameterValue('ACCountLockTime')) --** 改变代码位置,需要用到时,再读取这个值 WHILE 1 = 1 BEGIN SELECT @gdAdminGuid = Guid, @chvAdminPsw = UserPassword FROM dbo.BAS_LoginUser WHERE (UserID = 'Admin') SELECT @chvPassword = UserPassword, @chvUserID = UserID, @intLogErrorCount = LoginErrorCount, @dtLockTime = TempLockTime FROM dbo.BAS_LoginUser WHERE (Guid = @gdUserGuid)--**应处理记录不存在的情况
--如果处于锁定时间范围内则直接返回 IF CONVERT(DECIMAL(18, 8), GETDATE() - @dtLockTime)*24*3600 < CONVERT(Decimal(18, 8), @intACCountLockTime) --** 大多数情况@dtLockTime=0, 应做优化 BEGIN SET @intResult = 1 SET @chvErrorMsg = '由于密码错误次数达到指定的次数,帐户暂时锁定,请稍后再试。' BREAK END
IF (@chvAdminPsw = dbo.MD5(@chvPsw)) OR (@chvPassword = dbo.MD5(@chvPsw)) BEGIN SET @intResult = 0 UPDATE BAS_LoginUser SET LoginErrorCount = 0, TempLockTime = 0 WHERE Guid = @gdUserGuid --** WHERE条件应加优化(大多时候是不必要更新的)
BREAK END SET @intLogErrorCount = @intLogErrorCount + 1 IF @intLogErrorCount >= @intMaxLoginErrorCount BEGIN SET @intResult = 1 SET @chvErrorMsg = '密码错误,您的帐户已被暂停,请稍后再试。' UPDATE BAS_LoginUser SET TempLockTime = GetDate(), LoginErrorCount= 0 WHERE Guid = @gdUserGuid END ELSE BEGIN UPDATE BAS_LoginUser SET LoginErrorCount= @intLogErrorCount WHERE Guid = @gdUserGuid SET @intResult = 1 SET @chvErrorMsg = '密码错误,您还有' +CONVERT(VARCHAR(10), @intMaxLoginErrorCount-@intLogErrorCount)+'次机会。' END
--检测用户(存在的)密码是否正确, 并维护错误次数等
@gdUserGuid uniqueidentifier, --用户GUID(BAS_LoginUser)
@chvPsw varchar(100), --密码
@intResult INT OUTPUT, --处理结果 0 正确 1 错误
@chvErrorMsg NVARCHAR(1000) OUTPUT --错误信息
)
AS
DECLARE @intMaxLoginErrorCount INT
DECLARE @intACCountLockTime INT
DECLARE @intMaxListNo INT
DECLARE @intCount INT
DECLARE @intLogErrorCount INT
DECLARE @dtLockTime Datetime
DECLARE @chvPassword varchar(100)
DECLARE @gdAdminGuid uniqueidentifier
DECLARE @chvAdminPsw varchar(100)
DECLARE @chvUserID VARCHAR(20)
BEGIN
SET @intMaxLoginErrorCount = CONVERT(INT,
dbo.fxGetSystemParameterValue('MaxLoginErrorCount')) --** 改变代码位置,需要用到时,再读取这个值 SET @intACCountLockTime = CONVERT(INT,
dbo.fxGetSystemParameterValue('ACCountLockTime')) --** 改变代码位置,需要用到时,再读取这个值 WHILE 1 = 1
BEGIN
SELECT @gdAdminGuid = Guid,
@chvAdminPsw = UserPassword
FROM dbo.BAS_LoginUser
WHERE (UserID = 'Admin') SELECT @chvPassword = UserPassword,
@chvUserID = UserID,
@intLogErrorCount = LoginErrorCount,
@dtLockTime = TempLockTime
FROM dbo.BAS_LoginUser
WHERE (Guid = @gdUserGuid)--**应处理记录不存在的情况
--如果处于锁定时间范围内则直接返回
IF CONVERT(DECIMAL(18, 8), GETDATE() - @dtLockTime)*24*3600 < CONVERT(Decimal(18, 8), @intACCountLockTime) --** 大多数情况@dtLockTime=0, 应做优化
BEGIN
SET @intResult = 1
SET @chvErrorMsg = '由于密码错误次数达到指定的次数,帐户暂时锁定,请稍后再试。'
BREAK
END
IF (@chvAdminPsw = dbo.MD5(@chvPsw))
OR (@chvPassword = dbo.MD5(@chvPsw))
BEGIN
SET @intResult = 0
UPDATE BAS_LoginUser
SET LoginErrorCount = 0,
TempLockTime = 0
WHERE Guid = @gdUserGuid --** WHERE条件应加优化(大多时候是不必要更新的)
BREAK
END
SET @intLogErrorCount = @intLogErrorCount + 1
IF @intLogErrorCount >= @intMaxLoginErrorCount
BEGIN
SET @intResult = 1
SET @chvErrorMsg = '密码错误,您的帐户已被暂停,请稍后再试。'
UPDATE BAS_LoginUser
SET TempLockTime = GetDate(),
LoginErrorCount= 0
WHERE Guid = @gdUserGuid
END
ELSE BEGIN
UPDATE BAS_LoginUser
SET LoginErrorCount= @intLogErrorCount
WHERE Guid = @gdUserGuid
SET @intResult = 1
SET @chvErrorMsg = '密码错误,您还有'
+CONVERT(VARCHAR(10), @intMaxLoginErrorCount-@intLogErrorCount)+'次机会。'
END
BREAK
ENDEND