Database Auditing

Question

Hi,

I would like to know the possible solutions for this scenario below regarding database auditing

Our requirement is to audit SQL Server databases when

• Update
• Insert
• Select (View)
• Delete rows from table
• Rename
• execute

statements occur on the database with the following information as the output 

• Username,database_name
• Date and time (dd-mmm-yyyy) (hh:mm:ss)
• The action carried out
• how long the process took to complete

These databases are very huge(1.5 tb)

I am currently exploring database auditing feature at the moment that fits all the needs except for 'how long the process took to complete'. any suggestions? 

Any other suggestions(apart from DB Auditing) on how to achieve this?

Thanks

Bhnau

Answer 1

Hello,

You can audit DML Statements like INSERT/UPDATE/DELETE with trigger. DDL Statements can be audited with DDL Trigger or with Extended Events

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Option 1Build your own solution with the help of DML and DDL triggers

Option 2

Use DMVs and fn_dblog() and build audit database

option 3

use profiler and dump out put to audit table

I found this blog on Microsoft partner website....

http://solutioncenter.apexsql.com/auditing-select-statements-on-sql-server/