系統預設有啟動對「登入失敗」的事件執行記錄。
在 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
沒有留言:
張貼留言