I've been tasked with performing auditing of "sysadmin" activity. I'm wondering how many of you might be doing this kind of auditing. If so what kind of problems/hurdles might you have run into in deploying an auditing system, and what tools (home grown and/or 3rd party) might you be using to do your auditing?
Here are my current audit requirements:
The scope of these Audit Trail requirements applies to data that has been identified as confidential data that resides on production SQL Server machines that is access by any account that is a member of the sysadmin fixed server role.
Physical Audit Trail:
A physical electronic audit trail file or method to produce a physical file that can be queried and/or browsed when needed must be available for each SQL Server instance that contains confidential data. The electronic components (log files, programs, etc) necessary to produce an audit trail must be maintained and readily available at least 30 days and must available via and archive for at least 1 year.
Each audit trail record will the contain WindowS LoginID (Windows account or SQL Server Login), the date and time of event, plus the type of action performed (SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, EXEC, etc) and whether the action was successful, or unsuccessful.
Auditing will happen continuously, while a SQL Server instance is up and running, because a complete set of audit trail records can not be collected if auditing is only done periodically.
Audit only Login and Logoff attempts for logins that are a member of the sysadmin server role will be audited.
Audit all SELECT, UPDATE, INSERT and DELETE commands against all tables that are identified to containing confidential data must be logged when the command is issued by a login that is a member of the sysadmin server role.
Audit all SQL Server commands performed by any LoginID (Windows Account or SQL Server Login) that has sysadmin rights.