0

I am trying to find out who created an audit (found under Audits) but I don't seem to have any luck. I've already tried many queries such as these:

Blog 1, Blog 2, Blog 3 And

SELECT *
FROM   [sys].[objects]
SELEcT * FROM sys.server_audits

They seem to work for all other objects, but not for audit. I need to know which user created the audit and which user altered it last. sys.server_audits does not help.

0

A trace isn't a database-level object, so none of those queries you were reading about had any chance. The default trace has this information, though, under event 117 (AUDIT CHANGE AUDIT EVENT) - assuming you haven't disabled it and that the audit was created recently enough that it's still there (it's a rolling trace).

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT * 
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 117
  ORDER BY StartTime DESC;
  • Thank you. This actually did not return any results. The trace was created 1 year ago. – LearnByReading Mar 29 at 13:04
  • @LearnByReading When the trace was created isn't relevant. It's how much data you're collecting in the trace. Older events will get pushed off. – Aaron Bertrand Mar 29 at 13:07
0

You may also want to check to see if a server audit specification was mapped to the audit and if the AUDIT_CHANGE_GROUP action type was included. The last modification to the audit and/or its state may end up being logged in the audit files as a result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.