SQL Server Auditing in 2008R2

Question

SQL Server Auditing in 2008R2

SQListic

SSCertifiable

Points: 6904
More actions
September 5, 2012 at 3:51 pm

#266337

Hi DBA'S
I am enabling the SQL Server auditing to trace the changes in databases, Actually i enabled one sample Audit 10 days back in a development box,
I selected 10 action groups mentioned here which i thought will be useful in tracing the changes
After two weeks i know there are lot of database changes were made by developers, but by noticing the SQL AUDIT LOGS i didnt find any chnages.
Scenario:
----------
I Created one SQL AUDIT
than I created SQL AUDIT SPECIFICATION in which i selected 10 ACTION GROUPS.
Is this is the correct way or am i missing something..
Suggest me please
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Irwan Tjanterik SSC-Addicted Points: 470 More actions · Answer 1

Hi

looks like you are wanting to keep track of DDL audit, if this is the case you need to use database triggers and this needs to be setup per-db, by doing this you can keep track and set where you want to log the audit trace.

If you are looking to just doing a table audit, I suggest you use either table trigger or the better way is CDC.

Regards

IT

Randall F Smith Grasshopper Points: 24 More actions · Answer 2

I don't think you enabled the right action groups. If you want to audit whenever create, alter or drop is executed on a schema object such as a table, view or stored procedure, then enable SCHEMA_OBJECT_CHANGE_GROUP. there's a good write up on this and other audit action groups at http://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/auditactiongroups/SCHEMA_OBJECT_CHANGE_GROUP.aspx. SQL action groups are very specific in what they audit.