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 INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id INNER 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' )
Friday, April 17, 2015
How to find when was a login deleted in SQL Server
Subscribe to:
Post Comments (Atom)
Nice Post, but for further idea i did search on google and found another helpful post see here: http://www.sqlserverlogexplorer.com/reading-sql-server-transaction-logs/
ReplyDelete