0

Is there a way to log any query every time one is executed and store information about it in a table? When someone executes a query, it would be looked at, If the query contains something specific in it then the query, the user who executed, and time it was executed would be stored in a table.

The only way I could see doing this right now is to have a stored procedure fire every X-amount of time to troll the query history. When one is found it is stored in the log table.

Aiden
  • 15
  • 3
  • There are commercial products that will do this for you, and much more reliably than something you'll put together yourself. It's a common requirement in highly regulated/audited environments. – alroc Jan 13 '16 at 21:45
  • @alroc Fair enough, but I would prefer to stay internal if there is a way. – Aiden Jan 13 '16 at 22:01
  • Then the answer to your question is yes, there is probably a way - depending on your version of SQL Server. If you're asking for complete instructions on *how* to do it, that's beyond the scope of Stack Overflow. You might also try getting to a SQL Saturday where [this session is presented](http://colleenmorrow.com/event/enterprise-level-auditing-on-a-standard-edition-budget-2/). But again, I can't stress enough - don't implement yourself what you can acquire elsewhere. It's likely better, and you won't be stuck supporting it forever. – alroc Jan 13 '16 at 22:10

1 Answers1

0

This Stackoverflow thread mentioning various ways to view query history in SQL Server Management Studio might be helpful to you.

One easy third-party tool that enables this functionality (albeit saving to an XML file not a SQL Server table) is ApexSQL Complete. More details here.

Cpt. Monac
  • 749
  • 3
  • 7
  • I can vouch for this answer on that thread: http://stackoverflow.com/a/5300022/2141458 I've worked in a shop that did their tracing more or less that way. Can be very handy to use a trace in that way, but takes some maintenance. – Jason Whitish Jan 19 '16 at 20:12