搜尋本站文章

載入中…

2010-11-13

檢視 SQL Server 「登入失敗(Login Failures)」報表

適用版本:SQL Server 2005、2008、R2。

系統預設有啟動對「登入失敗」的事件執行記錄。

在 SSMS 管理工具上,可以使用以下的步驟來檢視此報表:
步驟01. 在「物件總管」上,連線到指定的執行個體。
步驟02. 展開「安全性」\「登入」。
步驟03. 在「登入」節點上,滑鼠右鍵,選擇「報表」\「標準報表」\「登入失敗」。
請參考下圖所示:

但在 SSMS 管理工具內的報表,在呈現細部資料上,有些問題,改要為匯出成 Excel 檔案後,就可以閱讀細部的資料。
步驟如下:
步驟01. 在報表上,滑鼠右鍵。
步驟02. 選擇「匯出」\「Excel」。
請參考下圖所示:




若需要以 Transact-SQL 方式來查詢登入失敗的紀錄,則是去查詢「預設的追蹤(default trace)」所記錄的檔案。
其實,前述的圖型介面的報表之資料,也是來自於「預設的追蹤(default trace)」。

請參考以下的範例:
declare @curr_tracefilename varchar(500); 
declare @base_tracefilename varchar(500); 
declare @indx int ;
declare @temp_trace table (
 Error int
 ,StartTime datetime
 ,NTUserName nvarchar(128)collate database_default 
 ,NTDomainName nvarchar(128) collate database_default 
 ,HostName nvarchar(128) collate database_default 
 ,ApplicationName nvarchar(128) collate database_default 
 ,LoginName nvarchar(128) collate database_default 
 ,SPID int
 ,ServerName nvarchar(128) collate database_default 
 ,TextData nvarchar (max) collate database_default );

select @curr_tracefilename = path FROM sys.traces where is_default = 1 ; 
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx  = PATINDEX('%\%', @curr_tracefilename) 
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

insert into  @temp_trace
 select Error, StartTime, NTUserName, NTDomainName, HostName, ApplicationName, LoginName, SPID, ServerName, TextData
 from ::fn_trace_gettable( @base_tracefilename, default ) 
 where EventClass = 20 --signifies login failed

select dense_rank () over (order by S.loginname) loginrank, (dense_rank () over (order by S.loginname))%2 as l_loginrank , T.Error
 , convert(nchar(20), T.StartTime,120) "HitDate", T.NTUserName, T.NTDomainName, T.HostName, T.ApplicationName
 , T.LoginName, T.SPID, T.ServerName, T.TextData
 , case when S.loginname is null then 'InvalidLoginName' else S.loginname end as loginname_1
 , case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488) 
                 then 4 --''Password Related Problems''
                 else case when T.Error in (18458,18459,18460) 
                                   then 2 --''Licensing Related Problems''
                                        else case when T.Error in (18452,18450,18486,18457) 
                                                          then 1 --''Authentication Related Problems''
                                                          else case when T.Error in(18451,18461) 
                                                                                then 5 --''Server''''s Mode of Operation''
                                    else case when T.Error in (17197) 
                                                                                                  then 6 --''Slow Server Response''
                                          else 3 --''Others'' 
                                     end
                            end
                                                 end
                          end
        end  "Type"
,       (dense_rank() over ( order by (case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488) 
                                                                                then 4 -- ''Password Related Problems''
                                                                                else case when T.Error in (18458,18459,18460) 
                                                                                         then 2 --''Licensing Related Problems''
                                                                                         else case when T.Error in (18452,18450,18486,18457) 
                                                                                                  then 1 --''Authentication Related Problems''
                                                                                                  else case when T.Error in(18451,18461) 
                                                                                                           then 5 --''Server''''s Mode of Operation''
                                                                                                           else case when T.Error in (17197) 
                                                                                                                    then 6 --''Slow Server Response''
                                                                                                                        else 3-- ''Others'' 
                                                                                                                        end
                                                                                                           end
                                                                                                  end
                                                                                         end
                                                                                end )))%2  as l1
,       (row_number() over ( order by (case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488) 
                                                                   then 4 --''Password Related Problems''
                                                                   else case when T.Error in (18458,18459,18460) 
                                                                                then 2--''Licensing Related Problems''
                                                                                else case when T.Error in (18452,18450,18486,18457) 
                                                                                         then 1 --''Authentication Related Problems''
                                                                                         else case when T.Error in(18451,18461) 
                                                                                                  then 5 --''Server''''s Mode of Operation''
                                                                                                  else case when T.Error in (17197) 
                                                                                                           then 6 --''Slow Server Response''
                                               else 3-- ''Others'' 
                                               end
                                          end
                                                                                         end
                                                                                end
                                                                   end ),T.StartTime desc))%2  as l2
from @temp_trace T 
left outer join sys.syslogins S on(T.LoginName = S.loginname)

請參考下圖所示:


參考資料:
預設的追蹤(Default Trace)之簡介,文章編號:S071006803
http://www.dbworld.com.tw/default.aspx