HOW TO TABLE DATA VALUE CHANGES MANUALLY DONE BY A PERSON

Question

HOW TO TABLE DATA VALUE CHANGES MANUALLY DONE BY A PERSON

safzalhussain

SSC Veteran

Points: 264
More actions
December 18, 2012 at 7:32 am

#286618

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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

For the future, you can use CDC (Change Data Capture) to get that kind of thing.

In order to track down past changes, you'll need to check the transaction log. Is the database in Full, Bulk Recovery, or Simple, recovery model? If Simple, then the data is gone and you can't track it down. If Full or Bulk, you should be able to get a log parser application to find the data changes in there.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323504 More actions · Answer 2

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!

safzalhussain SSC Veteran Points: 264 More actions · Answer 3

thanks for your prompt response.

Please suggest the easiest method to cofigure for tracking in sql server 2005 express edition.

we dont have a dba expert so can it be configured with limited knowledge of sql

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

safzalhussain (12/18/2012)
Please suggest the easiest method to cofigure for tracking in sql server 2005 express edition.

Triggers.

CDC and Change tracking are SQL 2008 features (you posted in a 2008 forum, so we assumed you were using SQL 2008).

Even that won't stop a sysadmin, they can just change the tracking table too. If you have a dishonest sysadmin, that's a management problem (what else are they changing without permission)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

safzalhussain SSC Veteran Points: 264 More actions · Answer 5

need assistance, how to track changes in sql server 2005 express edition using triggers

Ant-Green SSC Guru Points: 113445 More actions · Answer 6

A quick Google search for "using triggers to track changes" yields many results

http://www.philosophicalgeek.com/2008/05/13/tracking-database-changes-using-triggers/

Lowell SSC Guru Points: 323504 More actions · Answer 7

stating the obvious here, you should immediately remove that persons access to SQL server, and change the sa /other admin passwords.

make sure whatever application that is used to connect does not connect as a sysadmin as well.

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!

GSquared SSC Guru Points: 260824 More actions · Answer 8

safzalhussain (12/18/2012)
need assistance, how to track changes in sql server 2005 express edition using triggers

ApexSQL sells a product that will do the work for you on that. Easier than trying to learn it yourself.

http://www.apexsql.com/sql_tools_audit.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mani Dev Right there with Babe Points: 717 More actions · Answer 9

Because this is only for one table..you can create trigger.

This should work well in your case.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

If you have not such tracking in place, then how do you know this person in IT changed his attendance records? If you can actually prove it, the charge is called "tampering with data" and the offender should be fired right the heck now!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

Jeff Moden (12/18/2012)
If you have not such tracking in place, then how do you know this person in IT changed his attendance records? If you can actually prove it, the charge is called "tampering with data" and the offender should be fired right the heck now!

+ 100 .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)