1

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.

2
  • 1
    What do you mean by manual data changes? Please elaborate. Mar 16, 2022 at 15:24
  • @SqlWorldWide - I have updated the question. I hope it is clearer now. Thanks!
    – gordon613
    Mar 16, 2022 at 17:40

1 Answer 1

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.

3
  • I apologize for not being clear about "manual data changes". I have updated the question and I hope it is clearer now that I mean modifications to the table without recourse to SQL commands.
    – gordon613
    Mar 16, 2022 at 17:39
  • 2
    @gordon613 Those changes still generate SQL behind the scene, and would be handled the same way as ad-hoc SQL. Mar 16, 2022 at 19:25
  • 1
    Correct. The Edit top x rows generates regular insert, update and delete commands. There is nothing special with that gui. Mar 16, 2022 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.