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

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment