1

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.

  • Use SQL Server Auditing. – Randolph West Jul 13 '18 at 2:26
  • irfan_m - See my answer here with a slew of detail that may work for your needs. You will need to test and maybe get innovative and adjust, etc. for your environment but potentially a starting point. SQL Server Auditing would work too if configured correctly. – Pimp Juice IT Jul 13 '18 at 6:04
  • I guess I forgot to send you a link to my answer: dba.stackexchange.com/questions/161752/… – Pimp Juice IT Jul 13 '18 at 18:37
1

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):

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/80938ab2-28a4-46eb-8b09-84ab9ae22079/how-to-find-the-user-who-performed-a-truncate-statement?forum=transactsql

enter image description here

Linux default trace location:

http://www.dharmendrakeshari.com/main-file-directory-structure-sql-server-linux/

enter image description here

0

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.

0

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://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-2017

Hope it hepls, good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.