Can one log manual data changes to an Azure SQL table using Set-AzSqlDatabaseAudit?

All we need is an easy explanation of the problem, so here it is.

This is a follow-on question to Logging just one table in an Azure SQL Database

It is possible to connect to my Azure SQL Server database using SSMS (Microsoft SQL Server Management Studio), and an appropriate username and password.

If someone does that, and then selects one of the tables, and does "Edit Top 200 Rows", then he/she can manually change one of the values of the table – by clicking inside and typing a new value – without recourse to SQL commands.

One could log such changes by building a second "History" table and adding a trigger to the first table, to add a row to the history table upon any update. However, I do not want to go down that route.

I wondered whether I could log such manual data changes using the PowerShell command Set-AzSqlDatabaseAudit?

Something like

Set-AzSqlDatabaseAudit `
  -ResourceGroupName "MyResourceGroupName" `
  -ServerName "MySqlServerName" `
  -DatabaseName "MyDatabaseName" `
  -AuditActionGroup "DATABASE_OBJECT_CHANGE_GROUP" `
  -AuditAction `
    "MANUAL_EDIT ON dbo.LoginTable BY public"

Note I am not necessarily suspicious of nefarious activity. There are legitimate use cases when admins need to log into the database to fix certain data issues, but I would want a "trail" of their activity.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

E.g. someone logs into the database and changes a value in the table
from ‘4.1’ to ‘4.2’, without using any SQL commands

That won’t happen. All modifications are done using SQL commands.

In your trigger case, the trigger contains an SQL command that does the modification.

When you say "manual data changes" we would need elaboration. If you use some tool that modifies your data, then that tool sends an SQL comment to SQL Server. Or calls a stored procedure, which in turn has an SQL command that modifies the table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply