Table Audit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Table Audit

Author

Topic

mathavanmani
Starting Member

5 Posts

Posted - 2006-11-10 : 09:13:21

Hai...
In our database...there are so many users...i wanted to know who have modified which data(deletion,insertion etc) for individual table and ..the complete audit.... can any one of u guide me..what to do

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 10:58:09

You can start with writing INSERT/UPDATE/DELETE triggers on transaction tables and capturing the login name and date time there.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-10 : 11:09:43

http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-11-10 : 15:18:00

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trigger,Triggers,audit%20changes

Kristen

mathavanmani
Starting Member

5 Posts

Posted - 2006-11-16 : 01:12:37

Hai all...
thaks for ur reply...is there any way to achieve that without using the Triggers.... because It will araise the performance related issues....

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 01:19:05

You could compare today's database against yesterday's backup - but that won't tell you that someone changed a record, used the modified data to make a fraudulent purchase, and then changed the record back to the original data.

Whereas an audit trail that records every change would do.

Unless your database is going to be huge, or have very frequent updates, don't worry about the performance of triggers. We use them on nearly all of our tables - for auditing purposes, and our databases have millions of records and GBs of data.

However, if you have a table where half the data needs to be audited, half not audited, and the non-audited data changed often (and the audited data rarely changes) its worth splitting into two tables so the audited-half only fires triggers when there is a real need.

Also avoid updating rows where no data is changed:

UPDATE MyTable
SET MyColumn = REPLACE(MyColumn, 'X', 'Y')

would be better if there was a WHERE clause:

WHERE MyColumn LIKE '%X%'

Kristen

mathavanmani
Starting Member

5 Posts

Posted - 2006-11-16 : 01:44:17

So using Trigger is the only option
Thanks Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 03:32:34

quote:

Also avoid updating rows where no data is changed:

UPDATE MyTable
SET MyColumn = REPLACE(MyColumn, 'X', 'Y')

would be better if there was a WHERE clause:

WHERE MyColumn LIKE '%X%'

Referring this? http://sqlteam.com/forums/topic.asp?TOPIC_ID=74943

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-16 : 04:15:46

quote:
Originally posted by mathavanmani

So using Trigger is the only option
Thanks Kristen

Yeah, for Audit trail triggers is most effecient method. check out this links you will get some more understanding on Audit Trail.

http://www.mindsdoor.net/SQLTriggers/AuditTrailTrigger.html

Chirag

http://chirikworld.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 04:45:30

"check out this links"

I find that method very heavy on resources, and very hard to produce reports against - as I described in the link earlier - which in turn points to this discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-17 : 04:27:58

what about enabling the c2 audit option would that be any use to you Mathavani type in c2 auditiing in BOL ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-18 : 01:14:55

quote:
Originally posted by Kristen

"check out this links"

I find that method very heavy on resources, and very hard to produce reports against - as I described in the link earlier - which in turn points to this discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52356

Kristen

Thanks will go through it..

Chirag

http://chirikworld.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-18 : 02:07:08

Just thought I would throw this out there: in mssql 2005, there are some nifty objects call DDL triggers that you can use to audit who has been changing your *schema* as well.

useful if you want to know whose neck to wring when the column your app depended on gets dropped

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-19 : 06:34:46

quote:
Originally posted by jezemine

Just thought I would throw this out there: in mssql 2005, there are some nifty objects call DDL triggers that you can use to audit who has been changing your *schema* as well.

useful if you want to know whose neck to wring when the column your app depended on gets dropped

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

you mean to say, that you can write triggers at the database level??

EDIT :- I found some usefull link on google.. Thanks for the pointer.
Chirag

http://chirikworld.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-19 : 10:47:27

you can write DDL triggers at the database or server level. server level would be for catching an event like CREATE/ALTER/DROP LOGIN.

Here are all the events you can fire on: http://msdn2.microsoft.com/en-us/library/ms189871.aspx

EDIT: here's an example of a trigger that logs every schema change in a database (scroll to the bottom to see the code):
http://www.elsasoft.org/SUMMER.AdventureWorks/tr_ddlDatabaseTriggerLog.htm

and here's one at the server level:
http://www.elsasoft.org/SUMMER.AdventureWorks/tr_ddltriglogin.htm

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources