Best way to audit table updates

Question

Best way to audit table updates

Isabelle2378

SSCertifiable

Points: 6174
More actions
December 5, 2005 at 11:40 am

#169844

Hi,
What is the best way to audit table update or inserts? We have a database that many people connect to and the manager wants to know who is updating what and when. Being new to SQL Server, I have done some research and wondered what the best way to do this:
- user the profiler -> security audit -> audit change audit event and save to a table?
- use triggers and stored procedures to automatically track changes to the tables (i've never written either before)?
Any suggestions? Thanks in advance!
Isabelle
Thanks!
Bea Isabelle

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would suggest some of the utilities from Redgate or idera that can present all changes in script format by reading the transaction logs.

it's full featured and would probably cost about the same as the time you spend creating and testing your own implementation of triggers and such, but with less overhead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Hi,

Thanks for your response. Unfortunately I don't have anything in the budget for this year to purchase any additional software. I'm looking for a way to do this with SQL Server. Isn't this kind of thing a standard thing to do? I'm just wondering if using Profile or triggers is the better way to go.

Thanks,

Isabelle

Thanks!
Bea Isabelle

John Rowan SSC Guru Points: 56440 More actions · Answer 3

I would use profiler if this is for a short term period so that you can satisfy your manager’s needs. If you need a long term/permanent solution, I would use triggers.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 4

Thanks for the update. I believe this will be a long term thing so I will start playing around with triggers.

Isabelle

Thanks!
Bea Isabelle

Ian Yates SSCoach Points: 19738 More actions · Answer 5

There have been a couple of articles in the past month about this issue and how to use triggers to store the data, management of the history tables, etc. Search the articles on this site - also read the discussions about the articles... One of them in particular went a LOT further in the discussion and discussed many pros and cons of different techniques using triggers, etc - eg, do you store data in the history table for inserts or just when you do updates?

A long read but worth it Good luck!

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

Here's the link to auditing - part 1 of a series by Steve (Jones) to help get you started...I believe he's written 4 in all on the same topic...

**ASCII stupid question, get a stupid ANSI !!!**

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 7

Great!

Thanks so much for all your replies.

Bea

Thanks!
Bea Isabelle

BReuter Ten Centuries Points: 1325 More actions · Answer 8

Your time may be better spent pushing for a move to SQL 2005. DDL Triggers are designed exactly for this purpose and make it very easy to log by querying the EventData() XML document.

Checkout this link. It references an example in the "AdventureWorks" database. http://msdn2.microsoft.com/en-us/library/ms187909.aspx