1ALTER 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
9AS
10BEGIN
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
22 DECLARE @ErrorCode INT
23 SET @ErrorCode = 0
24
25 DECLARE @TranStarted BIT
26 SET @TranStarted = 0
27
28 IF( @@TRANCOUNT = 0 ) --如果当前活动事务为0,开始事务并设置事务参数为1
29 BEGIN
30 BEGIN TRANSACTION
31 SET @TranStarted = 1
32 END
33 ELSE
34 SET @TranStarted = 0
35
36 DECLARE @DateTimeNowUTC DATETIME --声明当前时间并获取当前时间
37 EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
38
39
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
55 IF ( @@rowcount = 0 ) --如果受影响行数为0,回滚事物并返回错误代码
56 BEGIN
57 SET @ErrorCode = 1
58 GOTO Cleanup
59 END
60
61 IF( @IsLockedOut = 1 ) --如果用户被锁定,也回滚,不允许操作
62 BEGIN
63 GOTO Cleanup
64 END
65
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
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
97 IF( @@ERROR <> 0 )
98 BEGIN
99 SET @ErrorCode = -1
100 GOTO Cleanup
101 END
102
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
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
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
139 IF( @TranStarted = 1 )
140 BEGIN
141 SET @TranStarted = 0
142 COMMIT TRANSACTION
143 END
144
145 RETURN @ErrorCode
146
147Cleanup:
148
149 IF( @TranStarted = 1 )
150 BEGIN
151 SET @TranStarted = 0
152 ROLLBACK TRANSACTION
153 END
154
155 RETURN @ErrorCode
156
157END
2 @ApplicationName NVARCHAR(256),
3 @UserName NVARCHAR(256),
4 @IsPasswordCorrect BIT,
5 @UpdateLastLoginActivityDate BIT,
6 @MaxInvalidPasswordAttempts INT,
7 @PasswordAttemptWindow INT,
8 @TimeZoneAdjustment INT
9AS
10BEGIN
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
22 DECLARE @ErrorCode INT
23 SET @ErrorCode = 0
24
25 DECLARE @TranStarted BIT
26 SET @TranStarted = 0
27
28 IF( @@TRANCOUNT = 0 ) --如果当前活动事务为0,开始事务并设置事务参数为1
29 BEGIN
30 BEGIN TRANSACTION
31 SET @TranStarted = 1
32 END
33 ELSE
34 SET @TranStarted = 0
35
36 DECLARE @DateTimeNowUTC DATETIME --声明当前时间并获取当前时间
37 EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT
38
39
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
55 IF ( @@rowcount = 0 ) --如果受影响行数为0,回滚事物并返回错误代码
56 BEGIN
57 SET @ErrorCode = 1
58 GOTO Cleanup
59 END
60
61 IF( @IsLockedOut = 1 ) --如果用户被锁定,也回滚,不允许操作
62 BEGIN
63 GOTO Cleanup
64 END
65
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
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
97 IF( @@ERROR <> 0 )
98 BEGIN
99 SET @ErrorCode = -1
100 GOTO Cleanup
101 END
102
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
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
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
139 IF( @TranStarted = 1 )
140 BEGIN
141 SET @TranStarted = 0
142 COMMIT TRANSACTION
143 END
144
145 RETURN @ErrorCode
146
147Cleanup:
148
149 IF( @TranStarted = 1 )
150 BEGIN
151 SET @TranStarted = 0
152 ROLLBACK TRANSACTION
153 END
154
155 RETURN @ErrorCode
156
157END