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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-22 : 10:31:21
|
| Lawson writes "I have inherited a SQL Server 2000 SP2 application with 3 seperate databases. Data entry is Access 2000 form based with linked SQL tables. Windows 2000 security is being used.I want to track all inserts/updates/deletes made to the tables and produce reports by user/date/type. I came up with 3 options. 1. Table level triggers - with modification info being written to a history table. There are some good examples on your website and I can implement this without any problem.2. SQL Profiler. I have created a custom template to filter only UPD/INSERT/DELETE statements with the results being written to a table in the same database. My problem is that profiling is done on a server-wide basis and not for individual databases (although I could filter again). In addition, I cannot specify a database table when using a script/SP to start the profiling.3. Transaction Logs - Since all database activity is recorded, is it possible to use Transaction Logs as an audit trial?I have gone through your existing forums/articles and would very much appreciate your comments on the above. ThanksLawson" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-05-22 : 11:32:36
|
| To use the tr logs you would need a log reader - see Lumigents offering - I think there is a lnk on the site.Triggers would probably be the simplest method - and it also gives you control over what data you want to accumulate. You could even create aggregate tables from the triggers for reporting.With the profiler you would have to make sure it is always running and configured correctly.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
davy_boy2000
Starting Member
28 Posts |
Posted - 2002-06-04 : 07:09:28
|
| I came across a similar request in the developers forum, and pointed the user to a package that I discovered called SQLLog. There are two versions, and the first one is Freeware. You specify the tables you want to monitor, and it creates the triggers for you. It also supplies a piece of software to view the Audit Logs.Goto http://www.rlpsoftware.comCheers,Davy |
|
|
|
|
|
|
Audit Trial Options - Triggers, SQL Profiler or Transaction Logs?