Microsoft Purview DevOps policies now includes all the typical permissions for SQL support personnel

This article is contributed. See the original author and article here.

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 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

• DevOps policies for Azure SQL MI (Private Preview): Click here to test-drive this new experience at no additional charge. Note, your Microsoft Purview account and Azure SQL MI will be allow-listed after you enroll.


• DevOps policies for Azure SQL Database (GA) and SQL Server 2022 (GA):
  
  
  
  • Quick overview video on YouTube – https://aka.ms/Microsoft-Purview-DevOps-Policies-Video
  
  
  • Concept guide: https://aka.ms/MicrosoftPurviewDevOpsPolicies
  
  
  • Quick start: https://aka.ms/quickstart-DevOps-policies
  
  
  
  

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.