Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Microsoft Purview DevOps policies now includes all the typical permissions for SQL support personnel
Published Apr 28 2023 10:22 AM 3,450 Views
Microsoft

IT/DevOps personnel need access to database and system metadata so that they can keep critical database systems healthy, performing to expectations and secure. Microsoft Purview DevOps policies, a subset of Microsoft Purview access policies, are specifically designed to provide IT/DevOps personnel with the access they need so that they perform their crucial job, while at the same time helping reduce the insider threat.

 

Microsoft Purview DevOps policies is a central, cloud-based experience. This is what the experience looks like:

 

Figure 1: Screenshot of DevOps policiesFigure 1: Screenshot of DevOps policies

 

In prior blogs, we announced the launch into General Availability (GA) of Microsoft Purview DevOps policies, first for SQL Server 2022, and subsequently for Azure SQL Database. Today, we are pleased to introduce the expansion of permissions related to role “SQL Performance Monitor”. This is one of two roles that can be provisioned as part of the Microsoft Purview DevOps policies.

 

Up to this point, this role gave a user (or group) access to hundreds to DMVs/DMFs. These DMVs/DMFs allow the user to review system parameters, identify performance bottlenecks, currently running queries, blocking issues, memory usage, file and index usage, active user connections, internal tasks, procedure execution stats and use of the Query Store, among others.

 

With the expansion, Microsoft Purview can now be used to grant access to all the typical commands that a persona tasked with performance monitoring and analysis requires.  The additional permission assignments enable the user (or group) that is assigned role SQL Performance Monitor to see data from most catalog views, which contain information about user objects and configurations. Furthermore, the user will now be able to run several DBCC commands that are used for system analysis and can even create and change Extended Event-sessions for system tracing for deeper analysis.

 

This capability will be rolled-out to all Azure regions that support Azure SQL and Microsoft Purview over the next few weeks.

 

Role definition detail

The following table lists the actions that are now part of the role definition for SQL Performance Monitor. The first four (in blue) were already part of the role and the ones that follow were recently added.

 

Microsoft Purview policy role definition

Data source specific actions

   

SQL Performance Monitor

Microsoft.Sql/Sqlservers/Connect

 

Microsoft.Sql/Sqlservers/Databases/Connect

 

Microsoft.Sql/Sqlservers/Databases/SystemViewsAndFunctions/DatabasePerformanceState/Rows/Select

 

Microsoft.Sql/Sqlservers/SystemViewsAndFunctions/ServerPerformanceState/Rows/Select

 

Microsoft.Sql/Sqlservers/Databases/SystemViewsAndFunctions/DatabaseGeneralMetadata/Rows/Select

 

Microsoft.Sql/Sqlservers/SystemViewsAndFunctions/ServerGeneralMetadata/Rows/Select

 

Microsoft.Sql/Sqlservers/Databases/DBCCs/ViewDatabasePerformanceState/Execute

 

Microsoft.Sql/Sqlservers/DBCCs/ViewServerPerformanceState/Execute

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Create

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Options/Alter

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Events/Add

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Events/Drop

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/State/Enable

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/State/Disable

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Drop

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Target/Add

 

Microsoft.Sql/Sqlservers/Databases/ExtendedEventSessions/Target/Drop

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Create

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Options/Alter

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Events/Add

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Events/Drop

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/State/Enable

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/State/Disable

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Drop

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Target/Add

 

Microsoft.Sql/Sqlservers/ExtendedEventSessions/Target/Drop

 

Conclusion

With that, we expect the SQL Performance Monitor role to cover all the typical scenarios where you need to provision access for dedicated support personnel to Azure SQL or SQL Server 2022 (Azure Arc-enabled), all from a simple, central and scalable experience.

 

Of course. we are always interested in which other scenarios you would like to see enabled via Microsoft Purview.

 

Recommended next steps

 

Version history
Last update:
‎Apr 28 2023 10:22 AM
Updated by: