View failed logins from default trace

First verify if the default trace is on:
SELECT* FROM sys.configurations 
WHERE configuration_id = 1568
If default trace is not on, then configure it:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO
Check for failed logins:
SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( 
                SELECT TOP 1
                f.[value]
                FROM    sys.fn_trace_getinfo(NULL) f
                WHERE   f.property = 2)), DEFAULT) T
        JOIN sys.trace_events TE 
                ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v 
                ON v.trace_event_id = TE.trace_event_id
                AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Login Failed' )
order by t.StartTime desc

The scripts are a snippets from a great article by Feodor Georgiev.

No comments

Post a Comment