How to find who updated the Azure Synapse sql database objects

Question

Hi All,

We have granted access to the Active Directory User groups in our Azure Synapse SQL database objects. I can see the AD group which has created or updated a database object, I am trying to see which individual user has done the change. Can someone please help me?

Thanks

Answer 1

To identify which individual user within an Entra ID group has updated or created a database object in your Azure Synapse SQL database, you'll need to have diagnostic settings enabled. Here's how you can do this:

1. Enable Azure Synapse Auditing:
   • Begin by enabling auditing on your Azure Synapse Analytics workspace. SQL Auditing logs all database activity, including object modifications, and provides insights into who performed specific actions.
2. Set Audit Logs Destination:
   • Configure the audit logs to be sent to an Azure Storage account, Log Analytics workspace, or Event Hub. Ensure that the logs are stored in a location that is easy to query.
3. Query the Audit Logs:
   • If you’ve sent your audit logs to a Log Analytics workspace, you can use Kusto queries to analyze the logs and determine which specific user performed the action. Below are some resources for querying the logs:
     • Microsoft Synapse Workspaces Logs
     • Microsoft Synapse Workspaces Big Data Pools Logs
     • Microsoft Synapse Workspaces Kusto Pools Logs
     • Microsoft Synapse Workspaces Scope Pools Logs
     • Microsoft Synapse Workspaces SQL Pools Logs
   • Check the ServerPrincipalName Field:
     • This field will show the specific user who performed the action, even if they were operating under an Entra ID group.

Please note that this solution will allow you to see these changes going forward. However, if diagnostic settings were not enabled at the time of the transaction, you won’t be able to retrieve information about past actions. See here.

Also just noting that my this response partially reworded by AI (ChatGPT) for better articulation. I have tested and validated the reponse to ensure it was still accurate.

---

If this solution resolves your issue, please click "Accept Answer" and upvote it so others in the community can benefit.