Share via

How to collect information of Users running Insert/Update/Delete Command on the Synapse Pool?

Pradyumn Joshi 60 Reputation points
2025-11-10T08:45:03.1933333+00:00

I am looking for a solution which collects information of users who have executed Insert/Update/Delete command on synapse.

Tried the Log Analytics Route but the table SynapseSQLPoolRequests only contains Statement type and not Login ID, DMVs store 10,000 records of data only and running a job frequently (querying this DMV) would be an overhead.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
Answer accepted by question author
  1. Pilladi Padma Sai Manisha 495 Reputation points Microsoft External Staff Moderator
    2025-11-10T10:00:21.5366667+00:00

    Hi @Pradyumn Joshi,
    Thank you for reaching out to Microsoft QA.
    To track which users are executing Insert, Update, or Delete commands in Azure Synapse, the most reliable and efficient way is to enable auditing on your Synapse dedicated SQL pool. Auditing captures detailed information about who ran what query, including the user's login ID and the type of command they executed.

    Here’s how you can set it up:

    First, go to your Synapse workspace in the Azure portal. Under the security settings, you’ll find an option to turn on auditing. When you enable this, the system will start logging all database activities, including the DML commands and it will store those logs in an Azure Storage account or other destinations you choose.

    This approach is better than trying to monitor dynamic management views (DMVs) or Log Analytics tables because those only keep a limited number of records and often don’t include the user information you need. Auditing solves both of these problems by providing a comprehensive and persistent log of who did what and when.

    So, in short, to track user identities running Insert/Update/Delete commands in Synapse, enable auditing on the SQL pool and regularly review your audit logs. This is the recommended way to get accurate, user-level visibility without adding overhead to your system.
    Reference Links:
    Auditing - Azure SQL Database and Azure Synapse Analytics | Microsoft Learn
    https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

    Follow-Up Questions:

    1. Is this a dedicated SQL pool or a serverless SQL pool?
    2. Has Diagnostic settings / Auditing been enabled on the pool? If yes, which Log Analytics workspace (name / workspace id)?
    3. Time window of interest (exact start/end UTC times) and example table(s) to focus on.
    4. Do we need real-time detection or historical (forensics) search?
    5. Any example statement text, login name, or client IP to filter on?
    6. Are there any compliance retention requirements for audit logs (how many days)?

    I hope this helps you get started with tracking user activities in your Synapse environment! Let me know if you have any other questions!

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-11-10T08:45:23.4366667+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To collect information about users executing Insert, Update, or Delete commands on an Azure Synapse Analytics dedicated SQL pool, you can utilize Dynamic Management Views (DMVs) and Log Analytics. However, as you noted, DMVs only store a limited number of records (10,000), which may not be sufficient for long-term monitoring.

    Here are some approaches you can consider:

    1. Using DMVs: You can query the sys.dm_pdw_exec_requests DMV to find details about currently executing requests, including the session_id and request_id. This can help you identify which users are executing specific commands. For example: 
         SELECT *
   FROM sys.dm_pdw_exec_requests
   WHERE command IN ('INSERT', 'UPDATE', 'DELETE');
      However, keep in mind that this will only give you real-time data and will not persist beyond the 10,000 record limit.
    2. Log Analytics: If you have set up Log Analytics, you can query the SynapseSqlPoolSqlRequests table to get information about SQL requests. While you mentioned that this table does not contain the Login ID, you might be able to correlate the request information with other logs that include user details. You can create custom queries to extract relevant data.
    3. Custom Logging: If the built-in options do not meet your needs, consider implementing a custom logging solution. You could create triggers on your tables to log user actions (Insert, Update, Delete) to a separate logging table that includes the user ID and timestamp. This would allow you to maintain a complete history of user actions without the limitations of DMVs or Log Analytics.
    4. Alerts and Monitoring: Set up alerts in Azure Monitor to notify you when certain thresholds are met, such as a high volume of Insert/Update/Delete commands. This can help you monitor user activity more effectively.

    By combining these methods, you can create a comprehensive solution for tracking user actions on your Synapse pool.

    References:

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.