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 |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-11-10 : 14:30:20
|
| Has anyone needed to implement some sort of audit logging concept over queries to specific tables / columns? If so, what solution did you implement? Are there ways in the base product of SQL Server (2005, SP2) to implement this or is the only option 3rd party tools?My requirement is a short term initiative to log all access to specific tables / columns. The monthly I need to report over this information.Any suggestions? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-10 : 14:51:59
|
| tkizer / spirit1:Thanks for the quick response. I might not have made myself too clear :). I need to audit SELECT queries over the data. Correct me if I'm wrong but this can not be accomplished by triggers or the service broker / queue approach, can it? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-10 : 14:53:01
|
| nope you can't do that. the only way for this is either to run a profiler trace or server side trace._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-10 : 15:41:51
|
| You will need to look into third party tools that do this.I believe Idera and Lumigent sell tools that are supposed to do this. $$$CODO ERGO SUM |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-18 : 12:53:35
|
| FYI - just to follow up and let anyone who cares know what I did. I ended up setting up a server side trace using 100 rollover files, keeping each file at the default of 5 MB (and we have plenty of disk space for these). I created a stored procedure that is executed by a SQL Server job every 8 hours that imports all the trace files (except the active trace file) into the database and purges the trace file off the OS. Seems to work pretty good so far.And if you're curious, I have to report over this data as well as auditing over our Oracle databases so I imported the trace files (and auditing data) into common table structures for this purpose.Thanks for the input everyone provided! |
|
|
|
|
|
|
Auditing