Auditing User Change in your DB

Auditing User Change in your DB:

In this article I will speak about the well-known and useful solutions in Microsoft SQL Server 2008.

Some of these solutions needs enterprise edition to work (check Microsoft web site for more info)

Auditing you database is most important thing, always we have to audit our database server our
server performance, query performance and data integrity and the most important thing is when it related to the user (Auditing user behavior) if someone delete a record, update or even insert unwanted record.

We always protect our database from unexpected behavior of users by using Least Privilege, if user need to make transaction we may give him insert permission no update no delete, and so on. But sometimes we need to know who is inserted this row, who is updating employee salary from 1000 USD to 1200 USD, who delete this record and so on.

For this scenario there are many solutions:

1.        CDC Change Data Capture:  (http://msdn.microsoft.com/en-us/library/bb522489.aspx ).

2.       Triggers: especially when there is DDL (http://msdn.microsoft.com/en-us/magazine/cc164047.aspx ).

3.       And there are another amazing solution that I discovered it today from Microsoft forum by SQLUSA is using OUTPUT Clause (http://www.sqlusa.com/bestpractices2008/output/ ).

I think these solutions will help us as DBA to monitor our end users so no one can blame you about the change on salary of any employee.

Written by:

                Elmozamil Elamir Hamid