Enable table level auditing in Azure SQL DB

Question

Enable table level auditing in Azure SQL DB

Vijay Kumar 2,061

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.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Alberto Morillo 35,506 MVP Volunteer Moderator

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 2,061 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 35,506 Reputation points MVP Volunteer Moderator

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.

Share via

Enable table level auditing in Azure SQL DB

0 additional answers

Your answer