Which is best SQL Server Trace or SQL Server Audit
Hi All, I am a DBA and I am having sysadmin privilege on the SQL Server. In our organization, there is a requirement from the internal auditor that "all the activities of sysadmins should be tracked". This includes database creation, Schema creation, schema modification, Insert, Update, Delete... Which of the following options is the best to achieve this, either "SQL Server Trace" or "SQL Server Audit". Thanks, K.P.Senthil Kumar
If you are on SQL Server 2012 or higher you can utilize SQL Server Audit for server-level events in Standard Edition. At the database-level though will require Enterprise. Starting in 2012 you can also filter your audits to only capture those events for sysadmin role. If you are on Standard Edition only, then tracing is your only option. Extended Events does not provide access to the security events that SQL Auditing has so you could only monitor the statement level activity. It would be much more work to sift through all that data to find out what command was issued for an INSERT or SELECT.