5

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?

Rigel1121
  • 2,022
  • 1
  • 17
  • 24
Angela Marie-Daley
  • 222
  • 2
  • 11
  • Very closely related: [Are ad-hoc read-only queries stored in SQL Server transaction log?](http://stackoverflow.com/questions/5516358/are-ad-hoc-read-only-queries-stored-in-sql-server-transaction-log). Putting a `SELECT` statement in a transaction still doesn't promote it to the log. – Jeroen Mostert Feb 24 '15 at 09:09

1 Answers1

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

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
  • Would there ever be a situation where the trace would not pick up the select query? – Angela Marie-Daley Feb 24 '15 at 10:59
  • @Schmitz: There is no need to add information about apexsql its no where related to OP's question. OP no where asked about audit. It seems to me more like advertiesment – Shanky Feb 24 '15 at 11:33
  • For practical purpose almost all transaction in SQL Server is logged the only thing is fn_dblog would not show information about select in this case. – Shanky Feb 24 '15 at 11:34
  • @Shanky - I'm not affiliated with Apex. The link provided clearly lists several ways of performing audits, starting with standard MS tech. Did you actually check the link before commenting? – SchmitzIT Feb 24 '15 at 11:37
  • @Schmitz: The point is I cannot see Op asked for any audit related stuff if you can please add above comment of yours in answer then it would mean that you are just trying to help and its not promotion in any way. Its just how people do. Again I am not sure what is need of that link – Shanky Feb 24 '15 at 12:06
  • @Shanky - I took the OP's question to mean that she is hoping to figure out a way to log the queries issued by their users, and hoped that wrapping the `SELECT` in a transaction might be a way of doing so (which we both established it does not). Auditing would allow exactly that. The apex link simply was one of the first few to pop up when I Googled. I don't see that as being an issue, more so since the page actually shows different methods of implementing auditing. – SchmitzIT Feb 24 '15 at 12:09
  • @Schmitz agree it shows about SQL Server trace but it also has out of box solution where it says about its tool. Anyways I would remove my downvote, it was not to say that your answer is incorrect but more to check that it is not just mere advertiesment. – Shanky Feb 24 '15 at 12:13