RE: HOW TO TABLE DATA VALUE CHANGES MANUALLY DONE BY A PERSON

SSC Guru

Points: 323504

December 18, 2012 at 7:41 am

safzalhussain (12/18/2012)
we have a database of attandance system in which daily attendance recorded. there is a table in which one of IT guy directly modify his record for his late coming enteries.
we need to track such changes in a table directly updated.
can someone please help us to track these crime updates.
awaiting for urgent response.

AFTER the edit has occurred, if you did not have one of the many capabilities for tracking this in place BEFORE the crime occurred, there is NO WAY to find out/prove it.

going forward, you can look into any of the following:

Change Data Capture

SQL Audit

Create your own Custom Trace

C2 Level auditing

Adding Custom Extended Events

Adding A Trigger to specific tables.

MAYBE, if the database is in FULL Recovery Model AND you have a Full Backup already taken, you could do a log backup, and use a log reader tool to find out exactly when it occurred...but the log only keeps the spid @ the time, and not the hostname/username for whodunnit info.

you'll only be able to set something up to capture this going forward, now that you know it's a real issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!