What events are tracked in Azure Sql audit Logs

Viewed 671

What type of events are tracked in Azure Sql Database auditing. Can we setup to audit is a specific column in a table is updated?

1 Answers

SQL Auditing supports the following action groups:

BATCH_STARTED_GROUP, BATCH_COMPLETED_GROUP, APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, BACKUP_RESTORE_GROUP, DATABASE_LOGOUT_GROUP, DATABASE_OBJECT_CHANGE_GROUP, DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP, DATABASE_OBJECT_PERMISSION_CHANGE_GROUP, DATABASE_OPERATION_GROUP, AUDIT_CHANGE_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, DATABASE_PRINCIPAL_CHANGE_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_ROLE_MEMBER_CHANGE_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_CHANGE_GROUP, SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP, SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, USER_CHANGE_PASSWORD_GROUP

If you are interested on auditing UPDATE statements then you need to use "BATCH_COMPLETED_GROUP". In addition, you need to specify UPDATE as audit action. Below an example of how to audit only UPDATE statements but I don't know a way to capture updating a specific column.

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 "UPDATE ON database::[AdventureWorksLT] BY [public]"  
 -RetentionInDays 60
Related