SQL Server Log/Save all queries

Question

Hi,

I want to log/save all queries applied onto my SQL Server 2016 Express, I used SQL Server Profiler but it does not have good searching options.

This task is related to security that is why I need to save all SQL queries on monthly bases and then perform search to make sure no malicious activities going on.

Please suggest.

Thanks.

Answer 1

Hi @Abdul Samad Patel ,

Welcome to Microsoft Q&A!
Agree with pituach. Please see this official document about SQL Server Profiler: https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15

See this short article about Extended Events Vs Profiler Trace: https://www.mytecbits.com/microsoft/sql-server/extended-events-vs-profiler-trace

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

I want to log/save all queries applied onto my SQL Server 2016 Express,

This sound like a very bad idea to do especially in production. Make sure that monitor the information for short time as you can.

I used SQL Server Profiler but it does not have good searching options.

You should use Extended Event for the task. This is the official documentation to get start on using extended event (try it):

https://learn.microsoft.com/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

This task is related to security that is why I need to save all SQL queries on monthly bases and then perform search to make sure no malicious activities going on.

This sound to me in first glance like a huge issue in security and performance. This design is not the way I would probably recommend to go with

You want security by storing information which open the security?!?

All the information should be secured stored in the database probably for best option

More information about the full scenario might help us give a bit more after understand a bit more :-)