Sql-server – SQL Server: Who created an audit and who altered it last

auditsql serversql-server-2016

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.

Best Answer

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;
Related Question