Can we audit the activity inside the SQL Server 2008 to capture SELECTs activity?

For the first time I though CDC will be useful to DBAs in auditing the activity on the database, but its not entirely.  It is equivalent to auditing functionalities and in short CDC is more poised towards the ETL for incrementatl data load and that uses an asynchronous capture mechanism that reads the transaction log and populates change capture tables with the row data and provides API's that provide access to that captured data in a number of ways.

Be aware if the CDC is enable for a large table, changes are captured in a corresponding capture table, which will grow in size over time.  As usual there should be a cleanup agent job that is created that removes data from the capture tables according to a retention period.  The default period for retention is 72 hours and with this configuration of the cleanup job this problem is under user control.  So better to monitor the growth if you are intended to disable the cleanup job if you wanted, understanding of course that the capture tables would continue to grow.

The simple concept of CDC for ETL scenario is possible in using the functionality for the audit purpose, but in the real terms this auditing means a different process. May be we can look forward to get more tips & techniques from Industry Professionals and MVPs in this regard. As the SQL 2008 is in beginning phase with CTP4 I'm sure Microsoft SQL development team do plan to provide auditing support in SQL Server 2008 with future CTP releases or may be after RTM release, who knows.

Excellent reference on CDC out there.