sql server audit for delete table and alter schema

Question

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,

Answer 1

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.

Answer 2

You can use Database level audit actions. Refer

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16#database-level-audit-actions

To understand details about the auditing features in SQL Server database, Refer

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver16

Hope this helps

Answer 3

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.

Answer 4

Thanks everyone, it was great help.