Need to implement some sort of auditing

Question

Need to implement some sort of auditing

NicDX

SSC-Addicted

Points: 414
More actions
November 24, 2008 at 2:29 am

#194396

I need to implement some auditing in one of our development environments.
The main goal is to be able to see what poeple were doing on the database in the event that some confidiential data goes walking.
What is the best way to implement this with as little a performance impact as possible.
I would not be interested in updates\deletes\inserts but only select statements being run on the db.
Thank you
FYI: We are running SQL Server Std edition

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The only way to audit SELECT queries is by running a trace. There is some performance impact, but if you write the trace file to a different disk then your database files, it usually acceptable.

You might also have a look at Idera's Compliance Manager.

It offers among other things also the option to audit SELECT statements.

[font="Verdana"]Markus Bohse[/font]

NicDX SSC-Addicted Points: 414 More actions · Answer 2

Thanks for the feedback. I will investigate these two options.

Benjamin Lotter-629969 Old Hand Points: 301 More actions · Answer 3

Here's another option but it would not be very practical in a development environment.

(1) Limit table access to stored procedures.

(2) Have the stored procedure perform the select and log to an audit table the details you want to know.

Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

The problem with going with stored procedures is that you'll have an exception somewhere and that will cause problems. If you can tightly control development, this can work, but it is hard to do.

Lumigent has a compliance tool as well, but a trace might be your best bet. Have it rollover files on a separate disk, archive those off to tape periodically. Most of them will never be used, so keep some amount of time (30 days or so) and then throw everything else into archives.