?
Solved

SQL Server Auditing and Maintaing Logs

Posted on 2014-04-02
5
Medium Priority
?
928 Views
Last Modified: 2014-04-07
Hi,

We are currently in process of building a large ERP solution and one of the requirement is to generate a report (We are using SSRS report Server) of who did what. Management wants a capability to be able to generate a report that could tell them x person created n records in Entity1, m records in Entity2 and so on.

So the most logical solution was to add a Log table for each entity and build a trigger for every Insert Update and Delete.

However, the same information can be extracted from the Transaction Logs as well. Atleast that is an idea I am working with. Can someone point me in the right direction here, as I have been entrusted to explore the possible solutions (Tools that can do that). The idea I am working on is that we should not be required to re-invent the wheel and there should be SQL Server tools that should be able to do that and much more.

Any help in this matter will be much appreciated and I understand, this is a subjective question but I will try my best to do the justice with most suitable response(s).

Thanks in Advance.
Amit
0
Comment
Question by:AmitJain001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:AmitJain001
ID: 39974231
And I forgot to mention, points will also be awarded to the expert(s) for bringing out valid pros and cons of this approach, or advises a better approach.

Thanks,
Amit
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 39976014
It looks like you're using SQL 2008 - have you checked into Change Data Capture (CDC)?

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

It's built to monitor tables for changes and write the details of those changes to a logging table that can later be queried for a summary report. This would be the easiest way I can think of to track table-by-table changes.

You could create triggers like you're suggesting, and that might be easy if all you care about is "this user inserted X records into table 1, updated Y records in table 2, and deleted Z records from table 3", but it will be difficult to track all the column-level changes via triggers (and what happens if you add new columns later). CDC would allow you to capture as much (or as little) detail as you want, and I think it would meet your needs.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39978206
I agree about CDC.  A big problem with using a TRIGGER is that more than likely the user is not available to the TRIGGER.

One caveat with CDC is that it is Enterprise Edition only.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39979290
Good call - I didn't realize it was an Enterprise-only feature.

As Anthony mentions, whatever user is making the DML change will also need rights to the place the trigger is writing the data tracking entries. Maybe not a big deal, but something to consider.
0
 

Author Comment

by:AmitJain001
ID: 39982407
Thank You Guys. I was initially inclined towards using a Third Party Tool such as ApexSQL LOG or PowerBroker Database for extracting information from Transaction Log but it seems SQL Server 2008 R2 has simplified extracting information from Log files using CDC.

Will definitely explore more in this regard. Thanks Ryan, yours is the most valuable feedback and certainly gives me something to dive into.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question