CODEWITHSUNDEEP
azureazure-sql-database
Ravi Shet
Ravi Shet

Reputation: 23

Auditing a specific Azure SQL Server database user

I have a SQL Server database (as a service) in Azure. I have a few SQL Server users. One out of those is used by the application which makes lots of calls (DML) to the database.

I want to audit the SQL statements that were executed by the users other than the one used by the application. By default, if I allow auditing all users it would simply flood the storage with data (since the account used by application makes the majority of calls). I tried enabling both the server as well as the database level auditing in the azure portal but could not find an option where I can specify the users who need to be audited.

Is there a way to achieve it?

Upvotes: 1

Views: 622

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15648

Azure SQL DB auditing enables you to set up granular auditing policies for requirements like yours.

You can use the PowerShell cmdlet Set-AzureRmSqlDatabaseAuditing to create such a granular policy. The -AuditAction parameter enables you to specify the objects and login actions that you would like to audit.

For example, using the following parameter value would audit any SELECT statement on table 'myTable' by the principal 'public':

-AuditAction 'SELECT ON dbo.myTable BY public'

Additional info can be found on this documentation.

Upvotes: 1

Related Questions