Are Select Queries Within Rollback statements Stored On The Log?

Question

I was wondering if the following SQL code would be stored on the log, so we could look back at a future date to see what a user has typed in when querying the database?

BEGIN TRAN
SELECT * 
FROM pictures p
INNER JOIN product pr
ON p.item_id = pr.item_id
ROLLBACK TRAN

I think that if the code is wrapped in a rollback clause, no record of what the user has typed in will be visible?

Answer 1

In short, no. Since no data changes are taking place, there's no need to store anything in the log. In fact, the ROLLBACK doesn't matter, even if it would be COMMITed, there's still no data changes taking place, and thus no logging.

DELETE, UPDATE and INSERT is recorded. SELECT is not. If you want to log those kind of queries, you can use a trace, use SQL Audit, build your own solution to log, or use a third party product tool.

Here's some information on different techniques:

http://solutioncenter.apexsql.com/auditing-select-statements-on-sql-server/

Here's more info on the SQL Audit:

http://blogs.msdn.com/b/sreekarm/archive/2009/01/05/auditing-select-statements-in-sql-server-2008.aspx