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 |
|
bl1wilson
Starting Member
3 Posts |
 Posted - 2006-03-08 : 15:49:10
|
| we are being ask to log and report specific user activity with insert, modify, and delete commands. looking for scripts, tools, or other methods you have deployed with success. my concern is efficiency without degradation.thanks in advance. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-08 : 17:39:42
|
| pretty vague question so here's a vague answer:Sounds like audit tables are what you want. One for each table you want to track user activity for. this is not something that "comes" with sql server you'll have to "roll your own". If all your writes happen through stored procedures, you can incorporate logging there. If your users interact directly with the tables (they usually shouldn't) then you'll have to use triggers on each table you want to log activity for.Be One with the OptimizerTG |
 |
|
|
bl1wilson
Starting Member
3 Posts |
Posted - 2006-03-08 : 18:20:05
|
| TG, that was vague enough ... thanks.it was also enough to point me where i believe i need to go.i appreciate your input.regards,bl |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-09 : 09:53:02
|
| Table auditing should be handled by triggers rather than stored procedures anyway. Are you going to duplicate the auditing code in every sproc, and how are you going to enforce it, and what about one-time data imports and updates? Triggers are the way to go. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-09 : 10:59:05
|
| In the majority of cases I'd agree with you, Blindman. But the following scenario could be an exception. If the architecture is a mulit-tier system where the middle tier uses a sinlge login for database writes but the application(s) support user level security as well as being aware of various "specific user activity" that isn't stored in the tables. Direct table access is not allowed. Onetime inserts and updates may not be applicable to the "specific user activity" that needs to be captured. The trigger code may not be aware of all the "specific user activity" that the b1wilson needs to capture. But the (one insert/update/delete per table) SPs could be aware of it by additional parameters. Anyway it is just an alternative for a specific situation that could be considered (even if rejected).Be One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-09 : 11:18:20
|
| Yes, middle-tiers with single-logins make auditing difficult. I usually get around this by allowing my sprocs to accept a "modifier" parameter from the middle-tier, assuming it has more specific information than the database. The parameter is optional, and if none is supplied then the trigger and table defaults will automatically populated the modifier column with the server login.But I also separate the functionality of maintaining audit data within the production table from archiving historical data in secondary tables. I don't want my sprocs to be aware of or dependent upon archive tables or their structures, so this is always handled by triggers on the production tables. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 13:07:06
|
| We have columns for Create and Update User (and date) in every table - and thus they get into the Audit tables too!Kristen |
|
|
|
bl1wilson
Starting Member
3 Posts |
Posted - 2006-03-09 : 13:46:28
|
| thanks for all of your input and discussion. very informative, welcomed, and appreciated.i will continue to monitor this thread as we develop our soultion.regards,bl |
|
|
|
|
|
|
|
|
sarbanes-oxley challenge