aspnet_Membership_ResetPassword

  1ALTER  PROCEDURE dbo.aspnet_Membership_ResetPassword
  2    @ApplicationName             NVARCHAR(256),
  3    @UserName                    NVARCHAR(256),
  4    @NewPassword                 NVARCHAR(128),
  5    @MaxInvalidPasswordAttempts  INT,
  6    @PasswordAttemptWindow       INT,
  7    @PasswordSalt                NVARCHAR(128),
  8    @TimeZoneAdjustment          INT,
  9    @PasswordFormat              INT = 0,
 10    @PasswordAnswer              NVARCHAR(128) = NULL
 11AS
 12BEGIN
 13    DECLARE @IsLockedOut                            BIT
 14    DECLARE @LastLockoutDate                        DATETIME
 15    DECLARE @FailedPasswordAttemptCount             INT
 16    DECLARE @FailedPasswordAttemptWindowStart       DATETIME
 17    DECLARE @FailedPasswordAnswerAttemptCount       INT
 18    DECLARE @FailedPasswordAnswerAttemptWindowStart DATETIME
 19    /**//*声明一大堆变量，对于密码操作的*/     
 20
 21
 22
 23    DECLARE @UserId                                 UNIQUEIDENTIFIER
 24    SET     @UserId = NULL
 25
 26    DECLARE @ErrorCode     INT
 27    SET @ErrorCode = 0
 28
 29    DECLARE @TranStarted   BIT
 30    SET @TranStarted = 0
 31
 32    IF( @@TRANCOUNT = 0 ) --如果当前活动事务数为0，则开始事务，并设置事务参数为1
 33    BEGIN
 34        BEGIN TRANSACTION
 35        SET @TranStarted = 1
 36    END
 37    ELSE
 38        SET @TranStarted = 0
 39
 40    SELECT  @UserId = u.UserId
 41    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
 42    WHERE   LoweredUserName = LOWER(@UserName) AND
 43            u.ApplicationId = a.ApplicationId  AND
 44            LoweredApplicationName = a.LoweredApplicationName AND
 45            u.UserId = m.UserId
 46      /**//*查询符合条件用户名的用户的用户ID*/
 47    IF ( @UserId IS NULL ) --如果ID不存在，回滚事务
 48    BEGIN
 49        SET @ErrorCode = 1
 50        GOTO Cleanup
 51    END
 52
 53    SELECT @IsLockedOut = IsLockedOut,
 54           @LastLockoutDate = LastLockoutDate,
 55           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
 56           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
 57           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
 58           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
 59    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
 60    WHERE @UserId = UserId
 61    /**//*查询符合此用户的用户ID的字段（查询结果是上面声明的变量列表）*/
 62
 63    IF( @IsLockedOut = 1 ) --如果用户被锁定，又回滚
 64    BEGIN
 65        SET @ErrorCode = 99
 66        GOTO Cleanup
 67    END
 68
 69    DECLARE @DateTimeNowUTC DATETIME
 70    EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
 71
 72    UPDATE dbo.aspnet_Membership
 73    SET    Password = @NewPassword,
 74           LastPasswordChangedDate = @DateTimeNowUTC,
 75           PasswordFormat = @PasswordFormat,
 76           PasswordSalt = @PasswordSalt
 77    WHERE  @UserId = UserId AND
 78           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )
 79            -----密码答案为空或密码答案等于输入参数
 80      /**//*更新表中字段，包括密码，格式化密码等。*/
 81
 82
 83
 84    IF ( @@ROWCOUNT = 0 )----如果受影响行数为0，即未更新
 85        BEGIN
 86            IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
 87            BEGIN
 88                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
 89                SET @FailedPasswordAnswerAttemptCount = 1
 90            END
 91            ELSE
 92            BEGIN
 93                SET @FailedPasswordAnswerAttemptWindowStart = @DateTimeNowUTC
 94                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
 95            END
 96            
 97            BEGIN
 98                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
 99                BEGIN
100                    SET @IsLockedOut = 1
101                    SET @LastLockoutDate = @DateTimeNowUTC
102                END
103            END
104
105            SET @ErrorCode = 3
106        END
107    ELSE
108        BEGIN
109            IF( @FailedPasswordAnswerAttemptCount > 0 )
110            BEGIN
111                SET @FailedPasswordAnswerAttemptCount = 0
112                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
113            END
114        END
115    /**//*此IF块又是处理密码尝试和锁定相关的，如果更新成功就不执行此IF快*/
116
117
118    IF( NOT ( @PasswordAnswer IS NULL ) ) --如果密码答案不为空
119    BEGIN
120        UPDATE dbo.aspnet_Membership
121        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
122            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
123            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
124            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
125            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
126        WHERE @UserId = UserId
127       
128        IF( @@ERROR <> 0 )
129        BEGIN
130            SET @ErrorCode = -1
131            GOTO Cleanup
132        END
133    END
134
135    IF( @TranStarted = 1 )
136    BEGIN
137    SET @TranStarted = 0
138    COMMIT TRANSACTION
139    END
140
141    RETURN @ErrorCode
142
143Cleanup:
144
145    IF( @TranStarted = 1 )
146    BEGIN
147        SET @TranStarted = 0
148        ROLLBACK TRANSACTION
149    END
150
151    RETURN @ErrorCode
152
153END
154

转载于:https://www.cnblogs.com/ruanbl/archive/2006/08/31/491039.html