sql server audit for delete table and alter schema

HuuM 46 Reputation points
2022-10-17T03:55:50.92+00:00

Dear Experts,

i am looking for setting up SQL Server Audit on my database,

i need it to capture if somebody drop any table or alter schema of table.

please guide,

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,912 questions
{count} vote

Accepted answer
  1. Alberto Morillo 33,001 Reputation points MVP
    2022-10-17T04:38:16.243+00:00

    You can create a DDL trigger to keep track of it or prevent this to happen. See the rollback statement below.

    CREATE TRIGGER safety     
    ON DATABASE     
    FOR DROP_TABLE   
    AS     
       PRINT 'Save change on a log'     
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
       ROLLBACK;    
    

    Inside the trigger you can run below statement to capture the user that is executing the action.

    SET @bd = 'UserName: ' + UPPER(SUSER_NAME())   
    

    On this article you will find how to log schema changes with triggers.

    In you still want too use Azure SQL Auditing you will find on this article how to track schema changes.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Sreeju Nair 11,856 Reputation points
    2022-10-17T04:29:54.173+00:00
    1 person found this answer helpful.
    0 comments No comments

  2. YufeiShao-msft 7,061 Reputation points
    2022-10-17T08:57:52.86+00:00

    Hi @KShahzad-8722,

    You can utilize SQL Server extended events, create a new audit object, choose whether you need Server Audit Specification or Database, such as Database, expand the node of the database you want to audit, click Security, right-click Database Audit Specifications and select New…

    specify a specification name, associate the specification with the audit object, for database object, specify a database, object, or schema as Object Class, the name of the audited object, and the audited login, you can see all actions that can be audited using SQL Server Auditing in drop-down list for Audit Acrion Type, including DELETE

    Or use SQL Server triggers, like DDL triggers can be used to track the DELETE statements

    -------------

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. HuuM 46 Reputation points
    2022-10-26T14:18:21.87+00:00

    Thanks everyone, it was great help.