Audit trail without triggers ?? How to ?

Question

Audit trail without triggers ?? How to ?

Yorgs Neil

SSC-Addicted

Points: 499
More actions
June 15, 2005 at 4:14 pm

#93209

Hello,
Any suggestions on creating an audit table to track :
Action Type \ Field Name \ Before Value \ After Value \Who Changed \When Changed

Action type >> I/U/D >> Insert / Update /Delete

Thanks
Yorgs

Viewing 5 posts - 1 through 4 (of 4 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

If it is allowed to use stored procedure remove Insert, Update and delete rights for application user on the tables. Do everything by stored procedures. Give rights to Exec rights on Stored procedure abd handle everything in stored procedures.

Or use the application (Front End) to do the task. With OOAD it wouldn't be very difficiult.

Regards,
gova

Yorgs Neil SSC-Addicted Points: 499 More actions · Answer 2

Hello govinn,

Thanks for your reply,

I know how to handle gen of an audit trail with the on insert,update, delete trigger. (I/U/D)

If the logic is not in a trigger how does it get called on an I/U/D event and sample code ???

New to envrionment.

Thanks

Yorgs

Lowell SSC Guru Points: 323518 More actions · Answer 3

Can you use the built in C2 Audit Functionality from SQL Server 2K?

that audits literally everything that happens, every end user activity(ie every sql command), as well as a lot of otehr things. i would bet the audit logs grow at a tremendous rate.

see http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx

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!

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

You can also checkout products like Lumigent Entegra that use trace files and database logs to audit all SQL Server activity, I have used this and it has even allowed me to forward notification emails to developers to fix their code, example email:

Subject: Entegra - Login Failed

The user myuser myuser attempted to log in to SQ1 from the computer WEB1, using an application that identified itself as "My Web Site". The login attempt failed.

--

This e-mail notification was automatically generated by Lumigent Entegra from the computer SQL1.

Lumigent Technologies - http://www.lumigent.com

Showed the developer of the Web site login page that they were not validating the User name entry field.

Andy