1
ALTER 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
11
AS
12
BEGIN
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![](/Images/OutliningIndicators/None.gif)
21![](/Images/OutliningIndicators/None.gif)
22![](/Images/OutliningIndicators/None.gif)
23
DECLARE @UserId UNIQUEIDENTIFIER
24
SET @UserId = NULL
25![](/Images/OutliningIndicators/None.gif)
26
DECLARE @ErrorCode INT
27
SET @ErrorCode = 0
28![](/Images/OutliningIndicators/None.gif)
29
DECLARE @TranStarted BIT
30
SET @TranStarted = 0
31![](/Images/OutliningIndicators/None.gif)
32
IF( @@TRANCOUNT = 0 ) --如果当前活动事务数为0,则开始事务,并设置事务参数为1
33
BEGIN
34
BEGIN TRANSACTION
35
SET @TranStarted = 1
36
END
37
ELSE
38
SET @TranStarted = 0
39![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
63
IF( @IsLockedOut = 1 ) --如果用户被锁定,又回滚
64
BEGIN
65
SET @ErrorCode = 99
66
GOTO Cleanup
67
END
68![](/Images/OutliningIndicators/None.gif)
69
DECLARE @DateTimeNowUTC DATETIME
70
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
71![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
82![](/Images/OutliningIndicators/None.gif)
83![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
117![](/Images/OutliningIndicators/None.gif)
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![](/Images/OutliningIndicators/None.gif)
135
IF( @TranStarted = 1 )
136
BEGIN
137
SET @TranStarted = 0
138
COMMIT TRANSACTION
139
END
140![](/Images/OutliningIndicators/None.gif)
141
RETURN @ErrorCode
142![](/Images/OutliningIndicators/None.gif)
143
Cleanup:
144![](/Images/OutliningIndicators/None.gif)
145
IF( @TranStarted = 1 )
146
BEGIN
147
SET @TranStarted = 0
148
ROLLBACK TRANSACTION
149
END
150![](/Images/OutliningIndicators/None.gif)
151
RETURN @ErrorCode
152![](/Images/OutliningIndicators/None.gif)
153
END
154![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
105
![](/Images/OutliningIndicators/None.gif)
106
![](/Images/OutliningIndicators/None.gif)
107
![](/Images/OutliningIndicators/None.gif)
108
![](/Images/OutliningIndicators/None.gif)
109
![](/Images/OutliningIndicators/None.gif)
110
![](/Images/OutliningIndicators/None.gif)
111
![](/Images/OutliningIndicators/None.gif)
112
![](/Images/OutliningIndicators/None.gif)
113
![](/Images/OutliningIndicators/None.gif)
114
![](/Images/OutliningIndicators/None.gif)
115
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
116
![](/Images/OutliningIndicators/None.gif)
117
![](/Images/OutliningIndicators/None.gif)
118
![](/Images/OutliningIndicators/None.gif)
119
![](/Images/OutliningIndicators/None.gif)
120
![](/Images/OutliningIndicators/None.gif)
121
![](/Images/OutliningIndicators/None.gif)
122
![](/Images/OutliningIndicators/None.gif)
123
![](/Images/OutliningIndicators/None.gif)
124
![](/Images/OutliningIndicators/None.gif)
125
![](/Images/OutliningIndicators/None.gif)
126
![](/Images/OutliningIndicators/None.gif)
127
![](/Images/OutliningIndicators/None.gif)
128
![](/Images/OutliningIndicators/None.gif)
129
![](/Images/OutliningIndicators/None.gif)
130
![](/Images/OutliningIndicators/None.gif)
131
![](/Images/OutliningIndicators/None.gif)
132
![](/Images/OutliningIndicators/None.gif)
133
![](/Images/OutliningIndicators/None.gif)
134
![](/Images/OutliningIndicators/None.gif)
135
![](/Images/OutliningIndicators/None.gif)
136
![](/Images/OutliningIndicators/None.gif)
137
![](/Images/OutliningIndicators/None.gif)
138
![](/Images/OutliningIndicators/None.gif)
139
![](/Images/OutliningIndicators/None.gif)
140
![](/Images/OutliningIndicators/None.gif)
141
![](/Images/OutliningIndicators/None.gif)
142
![](/Images/OutliningIndicators/None.gif)
143
![](/Images/OutliningIndicators/None.gif)
144
![](/Images/OutliningIndicators/None.gif)
145
![](/Images/OutliningIndicators/None.gif)
146
![](/Images/OutliningIndicators/None.gif)
147
![](/Images/OutliningIndicators/None.gif)
148
![](/Images/OutliningIndicators/None.gif)
149
![](/Images/OutliningIndicators/None.gif)
150
![](/Images/OutliningIndicators/None.gif)
151
![](/Images/OutliningIndicators/None.gif)
152
![](/Images/OutliningIndicators/None.gif)
153
![](/Images/OutliningIndicators/None.gif)
154
![](/Images/OutliningIndicators/None.gif)