Blog

Determine when a SQL Server user was removed/dropped from a Database

October 10, 2018

Recently I had a recurring issue where a user was being inexplicably dropped from the a SQL Server database.

I used this query to quickly identify the user/application that was removing the user accounts:

DECLARE @traceLog VARCHAR(256)

SELECT @traceLog = CAST(value AS VARCHAR(256)) 
FROM  ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
  AND property = 2

SELECT TextData,
       NTUserName,
       HostName,
       ClientProcessID,
       ApplicationName,
       LoginName,
       EventClass,
       TargetUserName,
       StartTime 
FROM ::fn_trace_gettable(@traceLog, DEFAULT) traceLog 
INNER JOIN sys.trace_events traceEvents 
ON traceLog.EventClass = traceEvents.trace_event_id
WHERE traceLog.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)
ORDER BY traceLog.StartTime