Enable table level auditing in Azure SQL DB

Question

Enable table level auditing in Azure SQL DB

Vijay Kumar 1,996

Hi Team,

How to enable table level auditing in Azure SQL DB and Azure SQL MI?

We want to only enable auditing on certain table not on entire database.

Answer 1

Alberto Morillo 27,781 MVP

You can set up the audit as shown below:

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"  
 -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"    
 -StorageAccountName "storageAccount"   
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP"   
 -AuditAction "SELECT ON dbo.myTable BY public"    
 -RetentionInDays 60

See the audit action available at the database level here.

More AuditActions at the table level:

-AuditAction "DELETE ON dbo.myTable BY public"
-AuditAction "UPDATE ON dbo.myTable BY public"
-AuditAction "INSERT ON dbo.myTable BY public"

Vijay Kumar 1,996 Reputation points

2022-04-27T20:28:46.75+00:00
Hi Alberto,

Few questions

Where to run above script?

How to setup alert for table drop/alter?
Alberto Morillo 27,781 Reputation points MVP

2022-04-27T20:48:00.377+00:00
That is a PowerShell script you can run from your Windows computer wihitelisted on Azure SQL firewall.

The script I gave is for tracking DML operations for schema changes use below cmdlet:

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]"

You can write the aduit logs on Azure Log Analytics and use query language as shown here. Here you will find sample queries of how to know who dropped or truncated a table.

Enable table level auditing in Azure SQL DB

0 additional answers

Activity