1

Is there a way to see a list of statements run by a specific user in Azure SQL? I have a user that complains about query performance. The queries are generated by a tool so he doesn't know the actual query text.

If I look in Azure Portal on that database under Query Performance Insight I get the top 5 performance queries. But his queries doesn't show up there. If I could somehow get the latest queries from that user it would be very useful. It doesn't matter if the solution is in Azure Portal web interface or as a query I run against some admin views in the database.

johanrex
  • 389
  • 5
  • 18
  • Please refer to https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal – Jim Xu Sep 03 '20 at 08:37

2 Answers2

1

You need to enable SQL Auditing or collect those queries with Extended Events as explained here (user and session information of each query).

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 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 'yourlogin':

-AuditAction 'SELECT ON dbo.myTable BY yourlogin'

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
1

I would also recommend using Query Store.

Change the settings to do the following

  • Clear Query Store
  • Change Query capture to capture all queries

Then review the reports in Query Store. Make sure change the storage also so it doesn't run out.

Rizwan
  • 318
  • 1
  • 5