Wednesday, October 31, 2018

Who dropped that table?

Who dropped that table?  Who altered that view?  Who added that column.... and so forth.  You'd like to think you'll always know what's going on in your databases, but unless NOBODY touches it but you, that's just not possible.  I can tell you war stories that will floor you!  Some other time with a glass of red...  Until then, this is a quick query to tell you who dropped or altered any object within any database on your server.   

       -- declarations
       DECLARE
              @current VARCHAR(255),
              @start VARCHAR(255),
              @index INT;

       -- find your trace path
       SELECT @current = path FROM sys.traces WHERE is_default = 1;

       SET @current = REVERSE(@current)
       SELECT @index = PATINDEX('%\%', @current)
       SET @current = REVERSE(@current)
       SET @start = LEFT(@current, LEN(@current) - @index) + '\log.trc';

       -- query on the eventclasses for delete and alter
       SELECT CASE EventClass WHEN 164 THEN 'Object:Altered'
              WHEN 47 THEN 'Object:Deleted' END [Action],
              DatabaseName,
              ObjectName,
              HostName,
              ApplicationName,
              LoginName,
              StartTime
       FROM::fn_trace_gettable(@start, DEFAULT)
       WHERE EventClass IN (164,47)
       AND EventSubclass = 0
       AND DatabaseID <> 2

 Your end result will vary.. but this is my output from one of my instances:









Now you go talk to Joe Shmo or Ms. Lenovo and figure out why they're editing/dropping objects in your database.  😏

See these for details on fn_trace_gettable and the object events I used in my query:



6 comments:

  1. In reality , most of the time the login name comes up as NULL.

    ReplyDelete
  2. Hi Z. I have to say, I've used this code many times on numerous different instances, and it has never come up NULL for me. Unsure what conditions this would happen in, but I'll check it out and let you know what I find.

    ReplyDelete
    Replies
    1. I wonder if its my SQL. This is what I use
      -- Example to catch Deletes
      SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime
      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
      JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
      WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete'

      Delete
  3. I do the same but reading from the transaction log

    https://tsql.tech/find-who-e-when-something-was-messed-up-in-order-to-restore-to-a-safe-point-using-the-transaction-log/

    ReplyDelete
  4. Woohoo! Link to this article in Brent's email today!

    ReplyDelete
    Replies
    1. Love that Mr. Ozar has pointed you this way! Hopefully you find it helpful!

      Delete