Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Yesterday one of my team members has truncated a few tables, but who have truncated those tables is still a mystery.
Can I have user based query log with the timing of execution? Or something where I can enable that functionality in future??
Currently, I am using SQL-Server 2017 on Linux.
Thank you in advance.
Yes, we had a similar issue as well--for payroll data! Try the default trace as shown in this blog (it appears that this is available for Linux as well):
Linux default trace location:
http://www.dharmendrakeshari.com/main-file-directory-structure-sql-server-linux/
The real question is: do you really want to do something like that? Or are there better ways (like pushing the data to another server and having them run their queries/updates/truncates there, then restricting their access in Prod).
The Brent Ozar team had a good talk about this in one of their Office Hours Podcasts.
Basically, the answer was something like: Imagine your boss asked you to log everything you did during the day at the office on a post-it note.
9:00 AM - Me - Sat at my desk
9:01 AM - Me - Turned my computer on
9:02 AM - Me - Checked my email
9:03 AM - Me - Opened SSMS
The time you'd take to do things (and your overall productivity) would go down the drain.
So be prepared for SQL Server to slow down, since it has to take an extra step and log every query it runs to a table.
I don't have experience with SQL Server on Linux but you can take a look at DDL triggers. Not sure if those work on Linux.
This kind of triggers help you to find out who did this kind of actions in your database. I used to have on my servers couple of years ago.
Here's a link for reference https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-2017
Hope it hepls, good luck!
