Depending on their industry sectors, many database professionals have to deal with audits at some stage, often removing vital years off their lives and inches off their hairlines! Having worked as a DBA in the financial industry, I've experienced both internal and external auditor visits on multiple occasions. In all cases, we pretty much had to drop all other work to ensure they were provided with the relevant information, or to implement the changes they required so we could provide the information in the future.
The auditors' levels of experience and understanding varied wildly. This was not their fault, as they are not paid to be database experts, but it could make them frustrating to work with. I spent a few days arguing with one who decided that I, as the DBA, should not be able to create logins and change permissions. As the database administrator, I felt it was sensible to let me administer the database! This is why we are paid, to be trusted to do our jobs properly. We reached a compromise to properly document the changes - a set of documents no one ever looked at - but I understood why it was necessary. I have met many DBAs who are frustrated day-to-day by the limitations put on them by auditors. Full separation of duties is not uncommon in the financial sector, so you can write your scripts, but someone else has to run them. Automated auditing has become a necessary part of the job description for many DBAs.
The key with any auditing is to decide clearly what actually needs to be recorded and reported on to pass the audit or satisfy your requirements. Some environments need to know when database or server objects are created, changed or dropped; some need to know when login permissions are changed; and some need to know when certain data is accessed. Different industries have to comply with their own individual legislation, which again changes the requirements for auditing. This all affects the amount of data to be stored and reported on, as well as the potential impact on the database.
SQL Server has long been weak in this area, and third-party products were really the only way to go until SQL Server 2008 introduced database auditing built on the new Extended Events functionality. SQL Server used to rely on C2 auditing, profiler or server side traces, or DML and DDL triggers. These solutions either imposed too much overhead on database performance, or required a lot of management and configuration. SQL Server 2008 introduces a highly configurable, lightweight solution that can fulfil almost all auditing requirements. One of its most impressive features makes it easy to have different audit configurations running at different times of the day, as well as varying audit specifications on different databases.
So it seems Microsoft has successfully addressed the auditing issues for SQL Server 2008, although it is only available in Enterprise edition, which, for some companies, can prove expensive to implement. Unfortunately, the IT world is not a simple place, and almost every environment I visit is still running SQL Server 2000 and 2005 - and, in some cases, even 6.5! These cannot be audited with the new functionality. Many environments require auditing across other database platforms as well, and it's not in Microsoft's interest to allow you to also audit Oracle and Sybase, for example. All of this leads to frustration for the DBAs and whoever is responsible for compliance in the IT department.
For more on SQL Server 2008 auditing, go to http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx.
For a SQL Server Auditing podcast from Colin Stasiuk, go to http://sqlserverpedia.com/blog/sql-server-2008/guest-podcast-auditing-your-database-server/.