1
ALTER PROCEDURE dbo.aspnet_Membership_UpdateUserInfo
2
@ApplicationName NVARCHAR(256),
3
@UserName NVARCHAR(256),
4
@IsPasswordCorrect BIT,
5
@UpdateLastLoginActivityDate BIT,
6
@MaxInvalidPasswordAttempts INT,
7
@PasswordAttemptWindow INT,
8
@TimeZoneAdjustment INT
9
AS
10
BEGIN
11
DECLARE @UserId UNIQUEIDENTIFIER
12
DECLARE @IsApproved BIT
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![](/Images/OutliningIndicators/None.gif)
22
DECLARE @ErrorCode INT
23
SET @ErrorCode = 0
24![](/Images/OutliningIndicators/None.gif)
25
DECLARE @TranStarted BIT
26
SET @TranStarted = 0
27![](/Images/OutliningIndicators/None.gif)
28
IF( @@TRANCOUNT = 0 ) --如果当前活动事务为0,开始事务并设置事务参数为1
29
BEGIN
30
BEGIN TRANSACTION
31
SET @TranStarted = 1
32
END
33
ELSE
34
SET @TranStarted = 0
35![](/Images/OutliningIndicators/None.gif)
36
DECLARE @DateTimeNowUTC DATETIME --声明当前时间并获取当前时间
37
EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
38![](/Images/OutliningIndicators/None.gif)
39![](/Images/OutliningIndicators/None.gif)
40
SELECT @UserId = u.UserId,
41
@IsApproved = m.IsApproved,
42
@IsLockedOut = m.IsLockedOut,
43
@LastLockoutDate = m.LastLockoutDate,
44
@FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
45
@FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
46
@FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
47
@FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
48
FROM dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
49
WHERE LOWER(@ApplicationName) = a.LoweredApplicationName AND
50
u.ApplicationId = a.ApplicationId AND
51
u.UserId = m.UserId AND
52
LOWER(@UserName) = u.LoweredUserName
53
/*查询此用户的信息,字段都是上面声明的有关于密码的变量*/
54![](/Images/OutliningIndicators/None.gif)
55
IF ( @@rowcount = 0 ) --如果受影响行数为0,回滚事物并返回错误代码
56
BEGIN
57
SET @ErrorCode = 1
58
GOTO Cleanup
59
END
60![](/Images/OutliningIndicators/None.gif)
61
IF( @IsLockedOut = 1 ) --如果用户被锁定,也回滚,不允许操作
62
BEGIN
63
GOTO Cleanup
64
END
65![](/Images/OutliningIndicators/None.gif)
66
IF( @IsPasswordCorrect = 0 ) --如果密码不正确
67
BEGIN
68
IF( @DateTimeNowUTC > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
69
BEGIN
70
SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
71
SET @FailedPasswordAttemptCount = 1
72
END
73
ELSE
74
BEGIN
75
SET @FailedPasswordAttemptWindowStart = @DateTimeNowUTC
76
SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
77
END
78![](/Images/OutliningIndicators/None.gif)
79
BEGIN
80
IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
81
BEGIN
82
SET @IsLockedOut = 1
83
SET @LastLockoutDate = @DateTimeNowUTC
84
END
85
END
86
END
87
/*处理密码相关的问题*/
88
ELSE
89
BEGIN
90
IF( @UpdateLastLoginActivityDate = 1 ) --如果更新最近登陆和活跃时间
91
BEGIN
92
UPDATE dbo.aspnet_Membership
93
SET LastLoginDate = @DateTimeNowUTC
94
WHERE UserId = @UserId
95
--更新最近登陆时间
96![](/Images/OutliningIndicators/None.gif)
97
IF( @@ERROR <> 0 )
98
BEGIN
99
SET @ErrorCode = -1
100
GOTO Cleanup
101
END
102![](/Images/OutliningIndicators/None.gif)
103
UPDATE dbo.aspnet_Users
104
SET LastActivityDate = @DateTimeNowUTC
105
WHERE @UserId = UserId
106
--更新最近活跃时间
107
IF( @@ERROR <> 0 )
108
BEGIN
109
SET @ErrorCode = -1
110
GOTO Cleanup
111
END
112
END
113![](/Images/OutliningIndicators/None.gif)
114
IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
115
----如果密码尝试次数或者答案尝试次数大于0(此时输入正确,则全部还原处世状态)
116
BEGIN
117
SET @FailedPasswordAttemptCount = 0
118
SET @FailedPasswordAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
119
SET @FailedPasswordAnswerAttemptCount = 0
120
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( DATETIME, '17540101', 112 )
121
SET @LastLockoutDate = CONVERT( DATETIME, '17540101', 112 )
122
END
123
END
124![](/Images/OutliningIndicators/None.gif)
125
UPDATE dbo.aspnet_Membership
126
SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
127
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
128
FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
129
FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
130
FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
131
WHERE @UserId = UserId
132
---使用上面的参数更新表
133
IF( @@ERROR <> 0 )
134
BEGIN
135
SET @ErrorCode = -1
136
GOTO Cleanup
137
END
138![](/Images/OutliningIndicators/None.gif)
139
IF( @TranStarted = 1 )
140
BEGIN
141
SET @TranStarted = 0
142
COMMIT TRANSACTION
143
END
144![](/Images/OutliningIndicators/None.gif)
145
RETURN @ErrorCode
146![](/Images/OutliningIndicators/None.gif)
147
Cleanup:
148![](/Images/OutliningIndicators/None.gif)
149
IF( @TranStarted = 1 )
150
BEGIN
151
SET @TranStarted = 0
152
ROLLBACK TRANSACTION
153
END
154![](/Images/OutliningIndicators/None.gif)
155
RETURN @ErrorCode
156![](/Images/OutliningIndicators/None.gif)
157
END
![](/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/None.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/ExpandedBlockStart.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/None.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/None.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/ExpandedBlockStart.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/None.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)
155
![](/Images/OutliningIndicators/None.gif)
156
![](/Images/OutliningIndicators/None.gif)
157
![](/Images/OutliningIndicators/None.gif)