Audit on Select statement

  • Hi, we have a couple of tables with very sensitive data in them and whilst we have to give a number of users select access to this table I want to be able to find out how many times they are accessing it (by column or table) and when. I've seen many option to audit delete, insert, update statements etc.. but is there an audit feature of SQL2005 I could use for Selects??..or maybe a trigger???

    Thanks

  • AFAIK the only way is by starting a trace and investigating it.

    cfr SQL Server and SOX @ http://www.sqlservercentral.com/articles/Security/3203/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Unfortunately, you will not be able to track this information with triggers. Therea re two types of triggers DDL and DML. DDL triggers only fire for schema changes like drops or creates and DML fire for insert,update,delete.

    I would allow them to view the table via stored procedure and within the stored procedure you can write to the audit table before displaying the data.

  • Your only other option is to turn on the logging option, but keep in mind that it is not 100% accurate because it can miss events.

  • Thanks for that, yes I'd seen that the 'Audit Schema Object Access Event' trace option might pick it up so I could use that. I'll test it. Could you let me know about 'turning on logging'. Where is that option and what will it pick up?

    thanks

  • Thanks for that, yes I'd seen that the 'Audit Schema Object Access Event' trace option might pick it up so I could use that. I'll test it. Could you let me know about 'turning on logging'. Where is that option and what will it pick up?

    Right-click the instance properties --> Security --> then tick the c2 auditing box . All the details of what is gather is in the link ALZDBA posted.

    Read through the article because it gives great insight to how to setup logging and what it does for compliance.

  • Thats great, thanks everyone! 🙂

  • Adam Haines (1/17/2008)Read through the article because it gives great insight to how to setup logging and what it does for compliance.

    My day started awfull with huge network problems and the collateral damage a dba has to absorbe, even if not involved at all, just suffer the nagging comments:sick:.

    But after reading this comment, all clouds have vanished. :w00t:

    Thanks for that :Wow:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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