locked
How to audit drop table RRS feed

  • Question

  • How to audit drop table in azure sql database ?

    I added

    DATABASE_OBJECT_CHANGE_GROUP

    event using powershell.

    but seems it only audit CREATE table , drop table didn't records.

    Saturday, August 18, 2018 3:57 PM

Answers

  • yes, there is feature audit in azure sql database, at server level as well as database level. I configured in my environment, it required a storage account where it save the .xel file.

    run the below command it will enable auditing  on that particular db:

    Set-AzureRmSqlDatabaseAuditing `
    -State Enabled `
    -ResourceGroupName "resourcegroupname" `
    -ServerName "ssqlinstancename" `  #ssqlinstancename.database.windows.net
    -StorageAccountName "strageaccountname" `
    -DatabaseName "dbname" `
    -AuditActionGroup 'SCHEMA_OBJECT_CHANGE_GROUP' `
    -RetentionInDays 8 `
    -AuditAction "DELETE ON schema::dbo BY [public]"

    Please avoid to use trigger if possible ,Trigger is not good for performance.

     
    Saturday, September 1, 2018 7:45 PM

All replies

  • How to audit drop table in azure sql database ?

    I added

    DATABASE_OBJECT_CHANGE_GROUP

    event using powershell.

    but seems it only audit CREATE table , drop table didn't records.

    Triggers are supported in the Azure Database and fit this scenario.

    Connect to the database that you want to monitor

    CREATE DDL Trigger on the database for the event DROP_TABLE as in this sample:

    CREATE TRIGGER RonenTrigger ON DATABASE   
    FOR DROP_TABLE AS   
       -- Do what ever you want here, including store information about the user that drop the tab;e
       -- or in this example ROLLBACK = no drop table is allow
       -- and return error
       RAISERROR ('You must disable Trigger "RonenTrigger" to drop the table!',10, 1)  
       ROLLBACK  
    GO

    You can send email to the sysadmin using the trigger (not recommended directly), you can store information in tables or files, and so on


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 19, 2018 8:50 PM
  • I would use DDL triggers for monitoring and logging

    https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-2017


    Please mark this as answer if it helps

    Sunday, August 19, 2018 9:21 PM
  • apart from trigger , no audit option for "drop table "?
    Wednesday, August 29, 2018 3:29 PM
  • yes, there is feature audit in azure sql database, at server level as well as database level. I configured in my environment, it required a storage account where it save the .xel file.

    run the below command it will enable auditing  on that particular db:

    Set-AzureRmSqlDatabaseAuditing `
    -State Enabled `
    -ResourceGroupName "resourcegroupname" `
    -ServerName "ssqlinstancename" `  #ssqlinstancename.database.windows.net
    -StorageAccountName "strageaccountname" `
    -DatabaseName "dbname" `
    -AuditActionGroup 'SCHEMA_OBJECT_CHANGE_GROUP' `
    -RetentionInDays 8 `
    -AuditAction "DELETE ON schema::dbo BY [public]"

    Please avoid to use trigger if possible ,Trigger is not good for performance.

     
    Saturday, September 1, 2018 7:45 PM
  • 'SCHEMA_OBJECT_CHANGE_GROUP' ` is what I am looking for thanks
    Monday, September 3, 2018 6:02 AM
  • Hi,

    >> Please avoid to use trigger if possible ,Trigger is not good for performance.

    This is very problematic statement to say in general as this is simply not correct . There are cases that trigger will provide the best performance. It is always  a type of "depend" issue like most questions.

    * You should remember that nothing come free and most Auditing solution get the information from the server, which mean it can inflorescence performance. Even Auditing which is not related to the server (for example Auditing proxy and getting all the information before any request come to the server, or Auditing in the application side... still cost performance - The cost is not always in the server side but for each task some resources must be payed)

    With that being said, and with the assumption that Azure has "Unlimited resources" it is true that other solutions might fit best for some cases :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, September 10, 2018 6:20 PM