Auditing a specific Azure SQL Server database user

Question

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?

Alberto Morillo · Accepted Answer · 2018-09-30 19:07:41Z

1

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.

answered Sep 30, 2018 at 19:07

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravi Shet Over a year ago

Thanks Alberto for responding. I will try your recommendation

Alberto Morillo Over a year ago

Thank you for your feedback, Have a great day!

Collectives™ on Stack Overflow

Auditing a specific Azure SQL Server database user

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related