ALTER PROCEDURE dbo.aspnet_Membership_GetPassword --获取密码
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@MaxInvalidPasswordAttempts INT,
@PasswordAttemptWindow INT,
@TimeZoneAdjustment INT,
@PasswordAnswer NVARCHAR(128) = NULL
AS
BEGIN
DECLARE @UserId UNIQUEIDENTIFIER
DECLARE @PasswordFormat INT
DECLARE @Password NVARCHAR(128)
DECLARE @passAns NVARCHAR(128)
DECLARE @IsLockedOut BIT
DECLARE @LastLockoutDate DATETIME
DECLARE @FailedPasswordAttemptCount INT
DECLARE @FailedPasswordAttemptWindowStart DATETIME
DECLARE @FailedPasswordAnswerAttemptCount INT
DECLARE @FailedPasswordAnswerAttemptWindowStart DATETIME
/**//*声明一大堆的变量*/
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )----若当前活动事务为0,开始事务
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1 --设置事务参数为1
END
ELSE
SET @TranStarted = 0
DECLARE @DateTimeNowUTC DATETIME --声明当前标准时间
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
/**//*调用存储过程。第一个参数在存储过程中好象没用到,哪里用呢?-_-!*/
SELECT @UserId = u.UserId,
@Password = m.Password,
@passAns = m.PasswordAnswer,
@PasswordFormat = m.PasswordFormat,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
/**//*又是aspnet_Applications/aspnet_Users/aspnet_Membership三表连接查询,条件为
输入参数@UserName/@ApplicationName*/
IF ( @@rowcount = 0 )---返回受上一语句影响的行数,即无结果就回滚
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )--是否锁住,如果是也回滚(锁定用户当然不让操作……)
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
IF ( NOT( @PasswordAnswer IS NULL ) ) --如果密码提示问题不为空(干嘛那样写?和前面的区别吗?)
BEGIN
IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
/**//*如果此用户的密码提示问题为空或者密码提示问题不为输入的密码提示问题*/
BEGIN
IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
/**//*如果当前时间大于计算出来的时间,如下*/
/**//*就是当打开密码提示问题窗口失败的时间的分钟数上加上@PasswordAttemptWindow参数的值*/
/**//*参照DATEADD函数*/
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC --密码失败尝试窗口打开时间
SET @FailedPasswordAnswerAttemptCount = 1
END
/**//*这时间计算还真有点乱,-_-!!*/
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @DateTimeNowUTC
END
/**//*如果尝试次数超过限制,则锁定用户,并设置最近锁定时间为当前时间*/
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
/**//*如果密码问题存在,但是尝试次数大于0*/
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
END
---好象是还原为初值了
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
/**//*更新数据表*/
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
IF( @ErrorCode = 0 )
SELECT @Password, @PasswordFormat
/**//*如果错误代码为0,则返回密码和格式化的密码(这里才是主要的东西)*/
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
@ApplicationName NVARCHAR(256),
@UserName NVARCHAR(256),
@MaxInvalidPasswordAttempts INT,
@PasswordAttemptWindow INT,
@TimeZoneAdjustment INT,
@PasswordAnswer NVARCHAR(128) = NULL
AS
BEGIN
DECLARE @UserId UNIQUEIDENTIFIER
DECLARE @PasswordFormat INT
DECLARE @Password NVARCHAR(128)
DECLARE @passAns NVARCHAR(128)
DECLARE @IsLockedOut BIT
DECLARE @LastLockoutDate DATETIME
DECLARE @FailedPasswordAttemptCount INT
DECLARE @FailedPasswordAttemptWindowStart DATETIME
DECLARE @FailedPasswordAnswerAttemptCount INT
DECLARE @FailedPasswordAnswerAttemptWindowStart DATETIME
/**//*声明一大堆的变量*/
DECLARE @ErrorCode INT
SET @ErrorCode = 0
DECLARE @TranStarted BIT
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )----若当前活动事务为0,开始事务
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1 --设置事务参数为1
END
ELSE
SET @TranStarted = 0
DECLARE @DateTimeNowUTC DATETIME --声明当前标准时间
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
/**//*调用存储过程。第一个参数在存储过程中好象没用到,哪里用呢?-_-!*/
SELECT @UserId = u.UserId,
@Password = m.Password,
@passAns = m.PasswordAnswer,
@PasswordFormat = m.PasswordFormat,
@IsLockedOut = m.IsLockedOut,
@LastLockoutDate = m.LastLockoutDate,
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.ApplicationId = a.ApplicationId AND
u.UserId = m.UserId AND
LOWER(@UserName) = u.LoweredUserName
/**//*又是aspnet_Applications/aspnet_Users/aspnet_Membership三表连接查询,条件为
输入参数@UserName/@ApplicationName*/
IF ( @@rowcount = 0 )---返回受上一语句影响的行数,即无结果就回滚
BEGIN
SET @ErrorCode = 1
GOTO Cleanup
END
IF( @IsLockedOut = 1 )--是否锁住,如果是也回滚(锁定用户当然不让操作……)
BEGIN
SET @ErrorCode = 99
GOTO Cleanup
END
IF ( NOT( @PasswordAnswer IS NULL ) ) --如果密码提示问题不为空(干嘛那样写?和前面的区别吗?)
BEGIN
IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
/**//*如果此用户的密码提示问题为空或者密码提示问题不为输入的密码提示问题*/
BEGIN
IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
/**//*如果当前时间大于计算出来的时间,如下*/
/**//*就是当打开密码提示问题窗口失败的时间的分钟数上加上@PasswordAttemptWindow参数的值*/
/**//*参照DATEADD函数*/
BEGIN
SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC --密码失败尝试窗口打开时间
SET @FailedPasswordAnswerAttemptCount = 1
END
/**//*这时间计算还真有点乱,-_-!!*/
ELSE
BEGIN
SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
END
BEGIN
IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
BEGIN
SET @IsLockedOut = 1
SET @LastLockoutDate = @DateTimeNowUTC
END
/**//*如果尝试次数超过限制,则锁定用户,并设置最近锁定时间为当前时间*/
END
SET @ErrorCode = 3
END
ELSE
BEGIN
IF( @FailedPasswordAnswerAttemptCount > 0 )
/**//*如果密码问题存在,但是尝试次数大于0*/
BEGIN
SET @FailedPasswordAnswerAttemptCount = 0
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
END
---好象是还原为初值了
END
UPDATE dbo.aspnet_Membership
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
WHERE @UserId = UserId
/**//*更新数据表*/
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
IF( @ErrorCode = 0 )
SELECT @Password, @PasswordFormat
/**//*如果错误代码为0,则返回密码和格式化的密码(这里才是主要的东西)*/
RETURN @ErrorCode
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END