Logging just one table in an Azure SQL Database

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

Our company is ISO27001 certified. As such, it is required for us to log database events.
We are taking an incremental approach, and first starting with logging events just on one table.

This particular table contains the id of the logged in user.

We would like to create logs of

  1. manual data changes to this table (the name of the SQL Server user who effected the change, and what he/she did)
  2. stored procedures that change the data on this table. (the id of the logged in user, the name of the SQL Server user who effected the change, and the statement of the SQL server stored procedure, including the parameters)
  3. schema change events (the name of the SQL Server user who effected the change, and what he/she did)

We do not need to log events that view data.

What is the best way to do this when using Azure SQL?

The following thread discusses various ways of building an extra table in the database, and logging events using triggers.
https://stackoverflow.com/questions/38437/how-to-track-data-changes-in-a-database-table

However I would have liked to take advantage of the database auditing available in Azure SQL, in part because the afore-mentioned approach could easily slow down our database.

I have set up a database audit using the portal – and it generates about 1.5GB of auditing data every day, which is unnecessary for my purposes.

Is there a way of confining the Azure database auditing to relate to just one table?

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

Yes, you can configure auditing for Azure SQL Database to filter down to a single object, but you will need to use the Azure PowerShell module. You cannot do it through the Portal.

The example below assumes you have already enabled auditing at the database level through the Portal. This script changes the Audit Action Group to "DATABASE_OBJECT_CHANGE_GROUP" and then adds several Audit Actions. The Audit Actions is where you will specify the DML actions for the table you want to audit. You will have to specify each stored procedure separately. It’s just a comma delimited list.

Set-AzSqlDatabaseAudit `
  -ResourceGroupName "MyResourceGroupName" `
  -ServerName "MySqlServerName" `
  -DatabaseName "MyDatabaseName" `
  -AuditActionGroup "DATABASE_OBJECT_CHANGE_GROUP" `
  -AuditAction `
    "INSERT, UPDATE, DELETE ON dbo.LoginTable BY public", `
    "EXECUTE ON usp_InsertLoginTable BY public", `
    "EXECUTE ON usp_DeleteLoginTable BY public"

https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabaseaudit?view=azps-7.3.0

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15#database-level-audit-actions

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