Product:
Microsoft SQL server 2019
Issue:
How do i log if a table is deleted or truncated in SQL server databases?
Solution:
Create a audit log file with this SQL command:
USE master CREATE SERVER AUDIT [Truncate_Audit] TO FILE (FILEPATH = N'c:\temp\logs' ,MAXSIZE = 1 GB ,MAX_ROLLOVER_FILES = 20 ,RESERVE_DISK_SPACE = OFF ) WITH (QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) ALTER SERVER AUDIT [Truncate_Audit] WITH (STATE = ON)
Then to make all database that start with letter A to be monitor run below SQL command:
DECLARE @command varchar(500)
SELECT @command = 'IF ''?'' LIKE ''A%'' BEGIN USE ?
EXEC(''Create database audit specification [DatabaseAuditSpecification-Truncate]
FOR SERVER AUDIT [Truncate_Audit]
ADD (Delete on database::[?] by [dbo])
WITH (STATE=ON)'')END'
EXEC sp_MSforeachDB @command
Now, do some add and delete of a table in your database to create some action to log.
Then to see the log enter this SQL command:
SELECT event_time, succeeded, object_id, object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,statement,file_name,audit_file_offset
FROM fn_get_audit_File ('c:\temp\logs\Truncate_Audit_*.sqlaudit',null,null) order by event_time DESC
You have to adjust the SQL code for your needs.
More Information:
SQL Server auditing with Server and Database audit specifications
https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
