Friday, February 15, 2013

SQL Server Object Modified! Who Did It?

Today I would like to share another useful way to take advantage of SQL Server's default trace, which by default is enabled on every installed instance of yours.  Let's say a developer recognizes that something broke in their application, and it would appear as though it was caused by the modification of a table.  But they don't know who did it, when it happened, nothing - they just know something has changed.  This actually happened to me this week, so it's very fresh in my mind!  While there are various third party tools and configurable items you can enable to track object creation/modification, etc., SQL Server's default trace is there for you by default and doesn't require you to do anything.

So, back to our example - what changed?  The first thing I would recommend is to query sys.trace_events and perhaps paste the results into Excel, remove the ones you likely won't use often and print it out as a quick  reference.  After all, time is of the essence when you are troubleshooting live issues.

Here is the query I used to create my quick reference:

SELECT  te.category_id
       ,tc.[name]
       ,trace_event_id
       ,te.[name]
FROM    sys.trace_events te
        INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id
ORDER BY tc.[name]
       ,te.trace_event_id

So, in this particular case you want to focus on the following subset of events:

Next, you want to pull up a query that will go through all five default traces, filtering for the events you are interested in.  I recently wrote a post on how to query all five default traces at once, so I won't go into that here.

Here is the query I use (I know it's in the Test database, so I'll filter on that also):
DECLARE @filename NVARCHAR(4000);

-- Current default trace
SELECT  @filename = CAST(value AS NVARCHAR(4000))
FROM    ::
        FN_TRACE_GETINFO(DEFAULT)
WHERE   traceid = 1
        AND property = 2;

SET @filename = LEFT(@filename,LEN(@filename) - CHARINDEX('\',REVERSE(@filename))) + '\log.trc'

-- Find auto growth events in the current trace file
SELECT  ftg.StartTime
       ,te.name AS EventName
       ,DB_NAME(ftg.databaseid) AS DatabaseName
       ,ObjectName
       ,'Developer' AS NTUserName
       ,ApplicationName
FROM    ::
        FN_TRACE_GETTABLE(@filename,DEFAULT) AS ftg
        INNER   JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE   DB_NAME(ftg.databaseid) = 'Test'
        AND te.category_id = 5  -- Objects
ORDER BY ftg.StartTime DESC


And... what have we found?  A developer, using SSMS, has altered an object called DarnDeveloper in the Test database this morning at around 10am!
I hope this helps you with your forensic analysis and investigative work!