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

Javier Andres Vasquez Posso 21 Reputation points
2022-03-03T17:21:54.583+00:00

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.'

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,890 questions
{count} votes

Accepted answer
  1. Oury Ba-MSFT 16,891 Reputation points Microsoft Employee
    2022-03-29T23:30:37.353+00:00

    @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.

    188085-image.png

    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

    188097-image.png

    188123-image.png

    188124-image.png

    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


3 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-03-03T19:36:46.737+00:00

    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.

    0 comments No comments

  2. Erland Sommarskog 102.4K Reputation points
    2022-03-03T22:38:57.74+00:00

    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
    
    0 comments No comments

  3. Seeya Xi-MSFT 16,451 Reputation points
    2022-03-04T06:24:12.473+00:00

    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.

    0 comments No comments