question

kpsenthilkumar86 avatar image
kpsenthilkumar86 asked

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
audit
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Server Audit but that's an Enterprise Feature. Interesting thought: Since sysadmins can do everything they want on an instance, how could you guarantee the sysadmin is not hiding some actions?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Their intention is something is better than nothing. Anyway though we are having highest privilege if we disable the Audit we need to give justification for that since changing the status of Audit will also be tracked.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Audit is fine if you have Enterprise. If not, instead of Trace, use Extended Events. They're more lightweight and have a smaller impact on the system.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do I need to enable the "Database Audit" on per database basis. is it possible to create a "single Database Audit" for the all the databases like sql trace(.trc). why because the requirement is to track all the activities of sysadmins on all the databases.
0 Likes 0 ·
You will create the Audit Specification in each database, but the "audit file" is created at the server level. So you can have it all write to one file or split it up by database, base it on how you want to manage it all. You can easily script the specification and cursor through each database to create it with T-SQL.
0 Likes 0 ·
shawn, thank you so much for your support.
0 Likes 0 ·
bjvista avatar image
bjvista answered
If you need help configuring the audit, Randy Smith over at UltimateWindowsSecurity.com has a free SQL Audit Wizard and a plethora of other SQL audit tips and information. I think you will find the audit wizard very helpful. Here's a link to it: https://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/wizard.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.