The default trace is a system-defined trace which resides in SQL Server directory ,which consists of 5 files with the size of 20 MB each one.
Below one is the script to find out the path of default trace where it is running or saved.
SELECT * FROM fn_trace_getinfo(default);
| traceid | property | value | |
| 1 | 1 | 2 | Configured trace options |
| 1 | 2 | C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc | Trace file name |
| 1 | 3 | 20 | Max file size for the *.trc file |
| 1 | 4 | NULL | Stop time for the trace session |
| 1 | 5 | 1 | Current trace status (1 = On and 0 = Off) |
After get the default trace path info, use the below query to fetch the trace information.
SELECT * FROM ::fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_16.trc’, default)
selectstarfromyogi 
SELECT e.name EventClassName,t.StartTime, v.subclass_name, t.*
FROM ::fn_trace_gettable(‘E:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_336.trc’, default) t
JOIN sys.trace_events e ON e.trace_event_id = t.EventClass
JOIN sys.trace_subclass_values v ON v.trace_event_id = e.trace_event_id AND v.subclass_value = t.EventSubClass
LikeLike
to find out deleted logins details :
SELECT TE.name AS [EventName] ,
v.subclass_name ,
T.DatabaseName ,
t.DatabaseID ,
t.NTDomainName ,
t.ApplicationName ,
t.LoginName ,
t.SPID ,
t.StartTime ,
t.RoleName ,
t.TargetUserName ,
t.TargetLoginName ,
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 Addlogin Event’, ‘Audit Add DB User Event’,
‘Audit Add Member to DB Role Event’ )
AND v.subclass_name IN ( ‘add’, ‘Grant database access’, ‘drop’, ‘Revoke database access’)
LikeLike