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://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15  

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

https://sqlship.wordpress.com/2011/06/30/how-to-capture-delete-and-truncate-t-sql-using-database-audit-specification/