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

  1. 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)

  2. 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

more ▼

asked Mar 03, 2011 at 11:16 PM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Mar 03, 2011 at 11:23 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
  • 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.

more ▼

answered Mar 03, 2011 at 11:39 PM

avatar image

10.8k 37 58 51

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 03, 2011 at 11:16 PM

Seen: 1109 times

Last Updated: Mar 03, 2011 at 11:16 PM

Copyright 2018 Redgate Software. Privacy Policy