SQL Server: Who created an audit and who altered it last?

Question

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.

Aaron Bertrand · Answer 1 · 2019-03-28T17:09:52.890

1

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;

edited Mar 28 '19 at 17:09

answered Mar 28 '19 at 17:00

Aaron Bertrand

175,645
25
380
589

Thank you. This actually did not return any results. The trace was created 1 year ago. – LearnByReading Mar 29 '19 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 '19 at 13:07

score 0 · Answer 2 · answered Mar 28 '19 at 21:33

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.

SQL Server: Who created an audit and who altered it last?

2 Answers2