unexpected changes in database tables in sqlserver

I am working on sqlserver 2005 and recently started facing a problem of unexpected changes in database tables.

I have an employee table with a datetime column which holds date of birth of all the associates.

Recently i the DOB of the employees is getting changed and when i enquired ...... everyone says that they have not touched the database neither from the UI or from backend.

This is being happening since 4 months and am not able to know what exactly is happening on DB

I want to know will there be any chance of change in data on tables in sqlserver 2005 without anyone doing any changes (updating the table)
How can i know if anyone is doing these changes with the following values (from which IP,value updated into which column, datetime)

Thought of writing triggers but dont know how to capture the values which i have mentioned.

Thanks in advance Usha

asked Mar 03 '11 at 11:16 PM in Default

ushabuddha
11 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

A trigger would certainly help here. You could use an 'instead of' trigger to avoid the update and keep the data untouched or use an 'after' trigger Take some action after the update has been made.

No example at the moment as I am on my phone.

answered Mar 03 '11 at 11:23 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Did any one restored backups overwriting your DB?
Is there any trigger on the DB that update DOB column?
It can be a stored procedure that updates the DOB column, So find out the procedure that uses the mentioned table (You can use sp_depends or Object dependency) make sure nothing updates DOB column.
If nothing helps find the users who have Update permission on that table and do an investigation.

If you are on SQL Server 08 and above (E.Edition or Dev Edition) you can use Auditing. or you can use custom audit triggers for that table capturing username and other details. Refer this simple blog for audit trigger.

answered Mar 03 '11 at 11:39 PM