Post reply

Sql Server Auditing tool

  • December 3, 2008 at 8:14 am

    #203774

    Hi all,

    Currently we have a database which is attached to our company website, users connect to the website under different logins but access the database under one sql login. There are tables which we want to capture edit history (who, what, when), we could create triggers on these tables but we were also thinking of getting a good auditing tool for future projects as well....

    Any suggestions?

    thanks

    Channa

  • December 3, 2008 at 9:56 am

    #907006

    try DDL Triggers

  • December 8, 2008 at 12:05 pm

    #909294

    Nothing beats good design.

    first question: Will you capture the who/what/when in the same table as where the table resides, or in a separate 'audit log' area?

    second question: how detailed does the 'what' answer have to be? the 'who' is easy - the login used to access your company website. the 'when' is easy - system time stamp. The devil is in the details of the 'what' you want to capture. Basic options are a quick summary of type of change:

    add - use timestamp to identify the record given proper design

    change - need to know generic change or details of before/after or in-between?

    delete - capture deleted content or just fact of deletion? Does anybody really delete anymore, rather than render 'invisible' (i.e., flagged as deleted)?

    The answers to these two questions will shape the next set of questions and determine your path. I suspect that if the desired results are not too complex, the answer will be 'build your own' both to ensure you meet requirements 100% as well as provide security over the transactional (audit) records. Personally, I don't know of any (affordable) tools, but I've not done any research - we chose to build our own for non-market reasons.

  • December 9, 2008 at 8:32 am

    #909700

    Hi,

    thanks for responding,

    so the answer to your first question: In a seperate Audit table

    and the answer to your second question, 'What' would be easy as the SQL that gets run on the table for instance:

    if the user updates a status of this table

    the sql that comes through to the underlying table (via Sprocs, etc)

    would look like this:

    Update sometable set status = 2 where user = 'blah'

    so there would be a column of Function which would capture the raw text of the above sql code.

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

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