Auditing / Tracking a record in a table

Question

Auditing / Tracking a record in a table

islamsr

SSC Rookie

Points: 30
More actions
March 16, 2008 at 5:23 am

#120465

Do we have some script to run with sys tables to get auditing/tracking information for a record. Like who inserted this record, how and when ???

Viewing 9 posts - 1 through 8 (of 8 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

This can be best implemented with either C2 auditing or DML triggers.

It should be mentioned that C2 auditing logs alot of information, maybe for than you need. This excessive logging consumes a lot of disk space and can degrade performance. You can enable C2 auditing by going to the server properties.

DML trigger require you to create triggers on each table that you want to participate in auditing. You can capture when a record is updated, inserted or deleted, who deleted it, and when it was deleted.

C2 auditing

http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

DML Triggers

script to create trigger on all tables http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31950/

http://www.sqlservercentral.com/scripts/Miscellaneous/31073/

More Info:

http://www.sqlservercentral.com/articles/Administering/auditingyoursqlserverpart1/917/

http://www.sqlservercentral.com/articles/auditingyoursqlserverpart2/933/

http://www.sqlservercentral.com/articles/auditingyoursqlserverpart3/946/

http://www.sqlservercentral.com/articles/Administering/auditingyoursqlserverpart4selectiveauditing/1340/

My blog: http://jahaines.blogspot.com

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

There's no way to go back and get this. A log reader might help if you really need one. Check out ApexSQL for a tool to help if you need it.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The short answer is NO and sys.tables won't tell you a thing about this.

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 4

SQL Server 2008 would have this feature out-of-the-box.

its called Change data capture (CDC)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Balmukund Lakhani (3/17/2008)
SQL Server 2008 would have this feature out-of-the-box.
its called Change data capture (CDC)

That's nice... got anything for 2005?

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 6

Balmukund

SSCertifiable

Points: 6648

March 17, 2008 at 12:34 am

#790228

I would repeat your answer "NO" 😀

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Balmukund Lakhani (3/17/2008)
I would repeat your answer "NO" 😀

Thanks... I was hoping that maybe I missed a trick... I'm fairly new to 2k5 and don't yet know all the "secrets". Thanks for the feedback. 😉

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 8

SQL Server 2008 would have this feature out-of-the-box.
its called Change data capture (CDC)

Interesting..

My blog: http://jahaines.blogspot.com