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

0 Comments

Leave Your Comment

Your email address will not be published.


about me

An information technology professional with twenty two year's experience in systems administration, computer programming, requirements gathering, customer service, and technical support.