Login Changes History | SansSQL

Thursday, October 17, 2013

Login Changes History

This query will gives the changes done to the logins in a SQL Server Instance.
This is based on the currently available Default Trace for that particular instance.

DECLARE @Tracepath nvarchar(max)
SELECT @Tracepath= [path] FROM sys.traces

SELECT TraceEvents.name AS [What Happened]
   ,SubClass.subclass_name AS [What Action]
   ,TraceTable.TargetLoginName AS [Who Was Affected]
   ,TraceTable.ApplicationName AS [From Which Application]
   ,TraceTable.LoginName AS [Who Did it]
   ,TraceTable.StartTime AS [At What Time]
FROM sys.fn_trace_gettable(@Tracepath, DEFAULT) TraceTable
JOIN sys.trace_Events TraceEvents ON TraceTable.EventClass = TraceEvents.trace_event_id
JOIN sys.trace_subclass_values SubClass ON SubClass.trace_event_id = TraceEvents.trace_event_id
AND SubClass.subclass_value = TraceTable.EventsubClass
WHERE TraceEvents.name like '%login%'

No comments:

Post a Comment