With SQL Server 2008 or higher, there is a powerful yet lightweight method to audit a SQL Server instance. But to manage and view the audits of your entire SQL Server environment, we have created the Centralized Auditing Framework that will parse, load, and report all of your audit logs.
From a high-level perspective, the architecture to audit sensitive operations for your SQL Server environment will be to:
- Turn on auditing on your various database and servers; you can find more information at http://msdn.microsoft.com/en-us/library/cc280386.aspx
- Have these audit logs go to one centralized location instead of a local folder
- Included in the Centralized Audit Framework is an SSIS package that will Parse through all of these audit log files.
- Populate a centralized database (also included) with audit dimension data (class, type, server names, etc.) and audit fact data (audit events separated by category of server, database, DDL, and DML actions).
Once the data is loaded, a nightly job will process the data to product report tables. These report tables can be viewed by SSRS reports (included).
The Auditing framework is available at GitHub to download the entire project.
Ayad Shammout's SQL & BI Blog 
Hi Ayad
This is a great project reference. Question do you have a branch of the project that doesn’t require the use of partitioning?
That is a good feedback, I will create another version of the script with no partitioning. You can modify the script and skip the section that creates the partition scheme and function and modify the table Create statement to create it on Primary file group.
Thanks,
Ayad
This is an awesome tips related to audit a SQL Server instance but I found good application from Lepide i.e., (http://www.lepide.com/sql-server-audit/) that is equipped with the important features and helps to track the entire changes made in SQL database with real-time monitoring. It audit every users activity made in SQL servers from a single centralized platform at granular level and get instant alert of all critical changes by sending customized email notification.