Enable IT personnel to monitor SQL health and performance while reducing the insider risk
Published Feb 15 2023 09:24 AM 3,688 Views

It is common that IT personnel tasked with monitoring the health and performance of database systems be given very high privileges such as SQL sysadmin. This enables them to do their job but comes with significant risks. Those privileges enable them to read or modify the data that other users in the organization store in those databases. That data is commonly referred to as “user data”. Sometimes user data can be very sensitive, for example, the consolidated financial information of a public company prior to being disclosed in an earnings report, a technological achievement that gives the company a competitive edge, and customer or employee information that must be protected to comply with privacy regulations. Sensitive data may be leaked or tampered with because of malicious intentions or simply poor security practices. When that happens, the company usually suffers financial damage and litigation against its officers.


Microsoft Purview DevOps policies support the Principle of Least Privilege (PoLP), which simply states that people should be given only the minimum access they need to be able to perform their job and no more. DevOps policies address the scenario of IT personnel tasked with monitoring the health and performance of database systems. This article showcases the experience for Azure SQL Managed Instance, the newest source supported for DevOps policies (now in private preview). Azure SQL Database and SQL Server 2022 are already supported, and the configuration steps are linked at the end.


First, register the Azure SQL MI in Microsoft Purview and enable Data use management. This means consenting that you would like to use Microsoft Purview to grant users access to the Azure SQL MI.




Second, navigate to the Data Policy App in Microsoft Purview and then to DevOps policies. Create a policy, selecting the Azure SQL MI data source in the prior step. Once you do that, the Data resource path will show <subscription name > resource-group name > data source name>. Next, select one of two role definitions "SQL Performance Monitor" or "SQL Security Auditor". Finally, select the Add/remove subjects to specify the Azure AD user(s) or group(s) that should be granted access:




Once you save the policy, Microsoft Purview will communicate it to the Azure SQL MI. It may take up to 5 minutes to be enforced.


To test, you can use SSMS. Connect with one of the AAD users that was granted access and then execute a query to get system metadata (DMVs and DMFs). For example, SQL Performance Monitor grants access to see the virtual file stats or the wait times. SQL Security Auditor grants access to see database encryption keys. An IT user granted SQL Performance monitor user should be able to perform the operations:




Next, try accessing a table in one of the databases. The IT user is denied, which means the crown jewels are protected.




Recommended steps:

Version history
Last update:
‎Mar 14 2023 09:13 PM
Updated by: