How to find out who truncate or delete my table in Azure SQL Database

Question

Hello guys

Please help me how to find out who truncate or delete my table in Azure SQL Database

I've tried doing these things but none works for me. query results are empty

---

SELECT
[Transaction ID],
Operation,
Context,
AllocUnitName

FROM
fn_dblog(NULL, NULL)
WHERE
Operation = 'LOP_DELETE_ROWS'

---

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
where dest.TEXT like '%delete%' or dest.TEXT like '%truncate%'
ORDER BY deqs.last_execution_time DESC

---

select * from sys.query_store_query_text where query_sql_text like '%delete%' or like '%truncate%'

please help me!!!

As an alternative option I have created a trigger to capture the deleted record and the time of the deletion but from here I cannot capture the user or the ddl that he executed

CREATE TRIGGER AfterDELETETrigger on [Asignaciones]
FOR DELETE
AS DECLARE @id INT,
@trabajador_key VARCHAR(50),
@fechaini date,
@fechafin date,
@horasteoricasdia int,
@idcentro int,
@turno varchar(100),
@coste_hora decimal (6,2)

SELECT @id = del.id FROM DELETED del;
SELECT @trabajador_key = del.trabajador_key FROM DELETED del;
SELECT @fechaini = del.fechaini FROM DELETED del;
SELECT @fechafin = del.fechafin FROM DELETED del;
SELECT @horasteoricasdia = del.horasteoricasdia FROM DELETED del;
SELECT @idcentro = del.idcentro FROM DELETED del;
SELECT @turno = del.turno FROM DELETED del;
SELECT @coste_hora = del.coste_hora FROM DELETED del;

INSERT INTO [AuditoriaAsignaciones](
ID
,[trabajador_key]
,[fechaini]
,[fechafin]
,[horasteoricasdia]
,[idcentro]
,turno
, coste_hora
,[ServerName]
,[ServerInstanceName]
,[Deleted_Time])
VALUES (@ID,
@trabajador_key,
@fechaini,
@fechafin,
@horasteoricasdia,
@idcentro,
@turno,
@coste_hora,
CAST( SERVERPROPERTY('MachineName') AS VARCHAR(50)),
CAST( SERVERPROPERTY('ServerName') AS VARCHAR(50)),
GETDATE());
PRINT 'got it deleted query.'

Answer 1

@Javier Andres Vasquez Posso Thank you for posting your Question on Microsoft Q&A forum and for using Azure services.

In my understanding you are trying to see who deleted or drop a table in Azure SQL database. Please let me know if my understanding is not correct.

As @Alberto Morillo mentioned above, you will need to enable auditing as first step.

Enable Auditing in Azure SQL

Navigate to Auditing under the Security heading in your SQL database or SQL server pane.

On Azure SQL DB you can have AUDIT data saved to Storage Account, Log Analytics and Event Hub. Log Analytics will be the easiest way to investigate this data.

You just need to go to Azure Portal > Monitor > Logs. Write your Kusto query and run it to get data

Create Alerts

You can create an alert at different resource levels from subscription down to Azure SQL resource to get notified in case a database was deleted, you will need to enable alert for activity log "Delete Azure SQL Database (Microsoft.Sql/servers/databases)" at the preference resource level. As shown in below example: -

Follow the link below to create an alert to get notified whenever performed an action on your Azure SQL Database.

Create an alert to get notification

Reference:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-and-log-analytics-better-together-part-1/ba-p/794833
https://techcommunity.microsoft.com/t5/azure-database-support-blog/learning-from-expertise-2-who-dropped-my-azure-sql-db/ba-p/2149276
https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-and-log-analytics-better-together-part-3-query/ba-p/1034222

Please let us know if this is helpful.

Regards,
Oury

Answer 2

You can't see into the past. SQL Server does not retain that information. If you did not install a trigger or auditing BEFORE it happened, you cannot find that information.

Answer 3

In addition to the other posts: your trigger is written on the incorrect assumption that triggers fires for every row. They don't. They fire once per statement, so you need to be able to handle multi-row operations.

In this particular example there is no need for variables, just to

INSERT tbl (...) SELECT ... FROM deleted

Answer 4

Hi @Javier Andres Vasquez Posso ,

Welcome to Microsoft Q&A!
Please see this article: https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/
I think it will give you some ideas.

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.