Friday, April 17, 2015

How to find when was a login deleted in SQL Server

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'
  )

1 comment:

  1. 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