January 17, 2008 at 7:36 am
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
January 17, 2008 at 7:56 am
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
January 17, 2008 at 7:56 am
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.
January 17, 2008 at 8:01 am
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.
January 17, 2008 at 8:14 am
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
January 17, 2008 at 8:31 am
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.
cfr SQL Server and SOX @ http://www.sqlservercentral.com/articles/Security/3203/
Read through the article because it gives great insight to how to setup logging and what it does for compliance.
January 17, 2008 at 10:17 am
Thats great, thanks everyone! 🙂
January 18, 2008 at 1:26 am
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