1

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?

Improve this question
2
  • What type of events are you trying to log exactly though? Schema changes, data changes, both? Reads on the table from the stored procedures? Etc. And what data points do you need to collect?...the what and when, but also the who?
    – J.D.
    Mar 9, 2022 at 14:07
  • 1
    @J.D. I updated the question. I hope it is clearer now. Thanks!
    – gordon613
    Mar 9, 2022 at 15:13

1 Answer 1

Reset to default
3

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://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabaseaudit?view=azps-7.3.0

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

Improve this answer
3
  • Thanks @Patrick Keisler. This looks extremely promising. Am checking this, though it may take me a few days to reply as I am having trouble with my PowerShell installation.
    – gordon613
    Mar 10, 2022 at 18:07
  • Thanks. It seems to work well. Am I correct in saying that each time one uses the Set-AzSqlDatabaseAudit then one needs to specify all of one's -AuditActions, or does it append to the existing -AuditActions?
    – gordon613
    Mar 15, 2022 at 17:32
  • 1
    @gordon613 Yes, the command must specify all actions each time you run Set-AzSqlDatabaseAudit. It does not append.
    – Patrick Keisler
    Mar 28, 2022 at 16:20

Your Answer

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

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