Sorry, was thinking of something else when I posted, not SQL Server Audit, as in the feature.

In terms of auditing the SELECT/INSERT/UPDATE/DELETE, a database audit specification will do this, but it audits the execution of the statement. A "failure" isn't a failure of the statement. It's another error. If someone executes a SELECT against a non-existent table, that's not a SELECT failure, that could be seen as a syntax error, or an object reference error, but the SELECT hasn't failed. An insert that has a duplicate key value is an FK error, not an insert error.

If I understand it correctly from limited use, you will get all executions of the statement, which is defined per object, and you'd have to sort through them, maybe filtering on some keyword in the logs. I'm not sure you can limit it to just one particular type of execution.

Understanding Audit - http://msdn.microsoft.com/en-us/library/cc280386%28v=SQL.100%29.aspx

Create DB spec - http://msdn.microsoft.com/en-us/library/cc280404%28v=SQL.100%29.aspx