MSSQL Default Trace

Check

SELECT *

  FROM sys.configurations

 WHERE configuration_id = 1568

SELECT traceid,

       value AS [TraceFileName]

  FROM ::fn_trace_getinfo(0)

 WHERE property=2

Enable

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

Query

SELECT loginname,       spid,       applicationname,       servername,       databasename,       objectName,       e.category_id,       cat.name AS [CategoryName],       textdata,       starttime,       e.trace_event_id,       CASE eventclass          WHEN 46 THEN 'Create'          WHEN 47 THEN 'Drop'          WHEN 164 THEN 'Alter'          ELSE CAST(eventclass AS VARCHAR)       END AS [EventClass],       CASE eventsubclass          WHEN 0 THEN 'Begin'          WHEN 1 THEN 'Commit'          ELSE CAST(eventsubclass AS VARCHAR)       END AS [EventSubClass],       e.name AS [EventName]  FROM ::fn_trace_gettable(CONVERT(VARCHAR(150),                                  (SELECT value                                     FROM sys.fn_trace_getinfo(0)                                    WHERE traceid=1 AND property=2)),                           DEFAULT) INNER JOIN sys.trace_events AS e         ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat         ON e.category_id = cat.category_id WHERE databasename = 'myDB'   AND objectname IS NULL   AND e.category_id = 5   AND e.trace_event_id = 46
eventsubclass0 = Begin1 = Commit
category_id5 = Object8 = Security Audit
trace_event_id 46 = Create 47 = Drop115 =164 = Alter

Bibliography