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 |
|
shmo
Starting Member
2 Posts |
 Posted - 2003-11-18 : 17:55:58
|
| I need to implement a tool which captures and report any changes to a database.Anyone have experience with SQL Audit, Omni Audit, Log Navigator?Any feedbacks, any others? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-18 : 20:17:07
|
| SQL Compare by Red Gate (http://www.red-gate.com/) is pretty good and can also compare two database structures, I haven't any experience with other products. But to be honest, the best way to do this is to lock down schema changes so that only one person, the DBA, can alter tables. If that's not feasible, your time is better spent trying to make it feasible. It's always better to lock your doors than it is to call in the best forensic teams in the world to figure out who burglarized your house (ounce-of-prevention worth pound-of-cure, etc.)You should also look at generating scripts for all of your database objects and checking them into a source control system, like Visual Source Safe or CVS. It is without question the best way to manage and control database changes. Search SQL Team for "source control" and you'll get a lot more info on it. |
 |
|
|
shmo
Starting Member
2 Posts |
Posted - 2003-11-19 : 12:32:57
|
| Thanks for the feedback, robvolk. I already used SQL Compare for comparing between 2 databases and works great. Locking down schema changes is great advice and will do just that for database changes. To be more clear what I am trying to achive, there is a rogue application or user in my domain that/who is executing inserts, updates and deletes. I have tried using SQL Profiler to catch this activity but due to performance impacts I now hesitate to use it. I need to capture user, machine and activity all day every day on every table. Any ideas? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-19 : 20:39:14
|
| Ahhhhh, I thought you meant schema changes.Profiler is probably your best bet, it's unlikely any other tool will perform better. Usually the overhead of Profiler is insignificant unless your server is already stressed out. I suggest setting up a trace capturing only "SQL:Stmt Started" events, and filter the TextData column for "DELETE", "INSERT" etc.Again, the best way to combat this problem is to restrict access/permissions. Deny all INSERT/UPDATE/DELETE permissions to all tables and views. Only stored procedures should be used to perform such actions anyway. If stored procedures aren't possible in the short term, it will at least get people's attention and may give you a lead as to who is doing it.Another option that is a lot more involved is to add triggers to your tables that capture and insert the user name into another table, or rollback the transaction if they are not on an approved list of names. This will interfere with proper activity by stored procedures however, and you'd have to put the trigger on every table, and it will have a bigger performance hit than Profiler will, so it's only a last resort. |
|
|
|
|
|
|
|
|
Sql Server Audit Tools