1 --解除锁定 2 ALTER 3 LOGIN Tear WITH PASSWORD = ‘Tear‘ UNLOCK 4 --启用帐户 5 ALTER LOGIN Tear ENABLE 6 --过期帐户用正确的密码登录，运行完后，延长密码策略中的“密码最长使用期限” 7 EXEC MASTER..xp_cmdshell  8      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear" -P"Tear" -c -b2000‘ 9 --用错误的登录名10 EXEC MASTER..xp_cmdshell 11      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear66" -P"Tear" -c -b2000‘12 GO 2     13 --用错误密码锁定帐户，具体次数根据密码策略调整14 EXEC MASTER..xp_cmdshell 15      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear" -P"Tear1" -c -b2000‘16 GO 617 --锁定情况下用正常密码18 EXEC MASTER..xp_cmdshell 19      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear" -P"Tear" -c -b2000‘20 GO 521 --禁用帐户22 ALTER LOGIN Tear DISABLE23 --错误密码登录24 EXEC MASTER..xp_cmdshell 25      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear" -P"Tear1" -c -b2000‘26 GO 427 --正确密码登录28 EXEC MASTER..xp_cmdshell 29      ‘bcp "DBA_Monitor.dbo.IPbase" in F:\IPdata\IPbase.txt -S127.0.0.1,5377 -U"Tear" -P"Tear" -c -b2000‘30 GO 3

--密码过期2014-08-13 21:22:41.370    登录    错误: 18487，严重性: 14，状态: 1。2014-08-13 21:22:41.370    登录    Login failed for user ‘Tear‘.  Reason: The password of the account has expired. [客户端: 127.0.0.1]--错误的登录名2014-08-13 21:23:57.630    登录    错误: 18456，严重性: 14，状态: 5。2014-08-13 21:23:57.630    登录    Login failed for user ‘Tear66‘. 原因: 找不到与提供的名称匹配的登录名。 [客户端: 127.0.0.1]2014-08-13 21:23:57.750    登录    错误: 18456，严重性: 14，状态: 5。2014-08-13 21:23:57.750    登录    Login failed for user ‘Tear66‘. 原因: 找不到与提供的名称匹配的登录名。 [客户端: 127.0.0.1]--错误的密码2014-08-13 21:24:00.720    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:00.720    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]2014-08-13 21:24:00.840    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:00.840    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]2014-08-13 21:24:01.000    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:01.000    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]2014-08-13 21:24:01.210    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:01.210    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]2014-08-13 21:24:01.380    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:01.380    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]2014-08-13 21:24:01.520    登录    错误: 18456，严重性: 14，状态: 8。2014-08-13 21:24:01.520    登录    Login failed for user ‘Tear‘. 原因: 密码与所提供的登录名不匹配。 [客户端: 127.0.0.1]--锁定情况下用正常密码2014-08-13 21:24:04.920    登录    错误: 18486，严重性: 14，状态: 1。2014-08-13 21:24:04.920    登录    Login failed for user ‘Tear‘ because the account is currently locked out. The system administrator can unlock it.  [客户端: 127.0.0.1]2014-08-13 21:24:05.050    登录    错误: 18486，严重性: 14，状态: 1。2014-08-13 21:24:05.050    登录    Login failed for user ‘Tear‘ because the account is currently locked out. The system administrator can unlock it.  [客户端: 127.0.0.1]2014-08-13 21:24:05.330    登录    错误: 18486，严重性: 14，状态: 1。2014-08-13 21:24:05.330    登录    Login failed for user ‘Tear‘ because the account is currently locked out. The system administrator can unlock it.  [客户端: 127.0.0.1]2014-08-13 21:24:05.630    登录    错误: 18486，严重性: 14，状态: 1。2014-08-13 21:24:05.630    登录    Login failed for user ‘Tear‘ because the account is currently locked out. The system administrator can unlock it.  [客户端: 127.0.0.1]2014-08-13 21:24:05.840    登录    错误: 18486，严重性: 14，状态: 1。2014-08-13 21:24:05.840    登录    Login failed for user ‘Tear‘ because the account is currently locked out. The system administrator can unlock it.  [客户端: 127.0.0.1]--禁用帐户后用错误密码登录2014-08-13 21:24:11.950    登录    错误: 18456，严重性: 14，状态: 7。2014-08-13 21:24:11.950    登录    Login failed for user ‘Tear‘. 原因: 评估密码时出错。 [客户端: 127.0.0.1]2014-08-13 21:24:12.300    登录    错误: 18456，严重性: 14，状态: 7。2014-08-13 21:24:12.300    登录    Login failed for user ‘Tear‘. 原因: 评估密码时出错。 [客户端: 127.0.0.1]2014-08-13 21:24:12.470    登录    错误: 18456，严重性: 14，状态: 7。2014-08-13 21:24:12.470    登录    Login failed for user ‘Tear‘. 原因: 评估密码时出错。 [客户端: 127.0.0.1]2014-08-13 21:24:12.600    登录    错误: 18456，严重性: 14，状态: 7。2014-08-13 21:24:12.600    登录    Login failed for user ‘Tear‘. 原因: 评估密码时出错。 [客户端: 127.0.0.1]--禁用帐户后用正确密码登录2014-08-13 21:24:16.430    登录    错误: 18470，严重性: 14，状态: 1。2014-08-13 21:24:16.430    登录    Login failed for user ‘Tear‘. Reason: The account is disabled. [客户端: 127.0.0.1]2014-08-13 21:24:16.550    登录    错误: 18470，严重性: 14，状态: 1。2014-08-13 21:24:16.550    登录    Login failed for user ‘Tear‘. Reason: The account is disabled. [客户端: 127.0.0.1]2014-08-13 21:24:16.680    登录    错误: 18470，严重性: 14，状态: 1。2014-08-13 21:24:16.680    登录    Login failed for user ‘Tear‘. Reason: The account is disabled. [客户端: 127.0.0.1]--达到最大并发连接数2014-08-13 21:26:19.660    登录    错误: 17809，严重性: 20，状态: 3。2014-08-13 21:26:19.660    登录    Could not connect because the maximum number of ‘20‘ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [客户端: 127.0.0.1]

  1 declare @startTime datetime  2 declare @endTime datetime  3 set @startTime = DATEADD(mi,-5,GETDATE())  4 set @endTime = GETDATE()  5 insert into Login_Errorlog  6 exec master.dbo.xp_ReadErrorLog 0, 1, N‘Failed‘, N‘login‘,@startTime,@endTime  7 insert into Login_Errorlog  8 exec master.dbo.xp_ReadErrorLog 0, 1, N‘connections‘, N‘to increase the maximum value‘,@startTime,@endTime  9  10 select IDENTITY(INT ,1 ,1) Id,a.LoginName,a.Ip,a.Reason,count(a.LoginName) FailedCount  11 into #LoginsFailed 12 from ( 13 SELECT substring(Text,charindex(‘‘‘‘,Text)+1,charindex(‘‘‘‘,Text,charindex(‘‘‘‘,Text)+1)-charindex(‘‘‘‘,Text)-1) LoginName 14       ,ltrim(rtrim(substring(Text,charindex(‘‘‘‘,Text,charindex(‘‘‘‘,Text)+1)+2,charindex(‘[‘,text)-charindex(‘‘‘‘,Text,charindex(‘‘‘‘,Text)+1)-3))) Reason 15       ,ltrim(rtrim(substring(Text,charindex(‘[‘,text),charindex(‘]‘,text)-charindex(‘[‘,text)+1))) Ip 16   FROM [dbo].[Login_Errorlog] 17 where LogDate>=dateadd(mi,-5,getdate()) 18   and Text like ‘Login%‘ 19   ) a 20   group by a.LoginName,a.Ip,a.Reason 21   order by count(a.LoginName) desc 22  23 select IDENTITY(INT ,1 ,1) Id,a.Ip,a.Reason,count(a.Ip) FailedCount  24 into #ConnectClosed 25 from ( 26 SELECT substring(Text,1,charindex(‘.‘,Text)) Reason 27       ,ltrim(rtrim(substring(Text,charindex(‘[‘,text),charindex(‘]‘,text)-charindex(‘[‘,text)+1))) Ip 28   FROM [dbo].[Login_Errorlog] 29 where LogDate>=dateadd(mi,-5,getdate()) 30   and Text like ‘%connections%‘ 31   ) a 32   group by a.Ip,a.Ip,a.Reason 33   order by count(a.Ip) desc 34  35 DECLARE @html_tb        VARCHAR(MAX) 36        ,@html_tb1       VARCHAR(MAX) 37        ,@Subject        VARCHAR(50) 38        ,@Recipients     VARCHAR(200) 39  40 SET @html_tb = ‘‘ 41 SET @html_tb1 = ‘‘ 42  43 IF EXISTS(SELECT 1 FROM #LoginsFailed) 44 BEGIN 45     SET @html_tb =  46         N‘<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style> 47                <H1>数据库登录失败检查(截至‘+convert(varchar(16),GETDATE(),120)+‘)</H1> 48                <table width = "100%" border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid"> 49                <tr><th>自增编号</th><th>登录名</th><th>登录IP</th><th>失败原因</th><th>失败次数</th> 50                </tr>‘ + 51         CAST( 52             ( 53                 SELECT td = Id 54                       ,‘‘ 55                       ,td = LoginName 56                       ,‘‘ 57                       ,td = Ip 58                       ,‘‘ 59                       ,td = Reason 60                       ,‘‘ 61                       ,td = FailedCount 62                       ,‘‘ 63                 FROM   #LoginsFailed 64                 ORDER BY 65                        Id 66                        FOR XML PATH(‘tr‘) 67             ) AS NVARCHAR(MAX) 68         ) + 69         N‘</table><br>‘ 70 END 71 IF EXISTS(SELECT 1 FROM #ConnectClosed) 72 BEGIN 73     SET @html_tb1 =  74         N‘<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style> 75                <H1>数据库连接数超限检查(截至‘+convert(varchar(16),GETDATE(),120)+‘)</H1> 76                <table width = "100%" border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid"> 77                <tr><th>自增编号</th><th>登录IP</th><th>失败原因</th><th>失败次数</th> 78                </tr>‘ + 79         CAST( 80             ( 81                 SELECT td = Id 82                       ,‘‘ 83                       ,td = Ip 84                       ,‘‘ 85                       ,td = Reason 86                       ,‘‘ 87                       ,td = FailedCount 88                       ,‘‘ 89                 FROM   #ConnectClosed 90                 ORDER BY 91                        Id 92                        FOR XML PATH(‘tr‘) 93             ) AS NVARCHAR(MAX) 94         ) + 95         N‘</table><br>‘ 96 END 97     SET @html_tb = @html_tb+@html_tb1 98     IF @html_tb<>‘‘ 99     EXEC msdb.dbo.sp_send_dbmail100          @profile_name = ‘Uest‘101         ,@recipients = ‘Uest@126.com‘102         ,@body = @html_tb103         ,@subject = ‘数据库登录失败检查‘104         ,@body_format = ‘HTML‘105 106 DROP TABLE #LoginsFailed107 DROP TABLE #ConnectClosed