how can i see the last 100 select statements that were run by any user

Question

how can i see the last 100 select statements that were run by any user

VoldemarG

Hall of Fame

Points: 3883
More actions
November 14, 2019 at 6:13 pm

#3697758

how can i see the last 100 select statements that were run by any user against a particular table?
Likes to play Chess

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Sorry,unless you have some auditing in place, before it happened, it is not possible.

you can get basically the last executed information for current connections from the DMV's, or using sp_whoisactive, for example, but not any history, SQL does not do that by default, you would have to add something for that, ad a monitoring tool, etc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner SSC-Dedicated Points: 31862 More actions · Answer 2

What Lowell said.
See also these links for explanations of SQL auditing options. But the bottom line (again, as Lowell said), you need to plan, install, and start that monitoring/auditing first. Only then can the setup start capturing the SQL queries you want

https://www.sqlservercentral.com/articles/making-good-use-of-sql-server-auditing-1

https://dba.stackexchange.com/questions/202357/log-all-sql-server-queries-with-their-durations

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

https://www.sqlshack.com/sql-server-auditing-server-database-audit-specifications/

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

HTH

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html