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