What parts of Policy Based management are people using?

[shueybubbles]

The Policy Based Management feature of SQL Server is implemented as SMO component. It has a bunch of code for creating and evaluating policies on both the server and the client.
As time has gone by our test coverage of PBM (aka DMF) has waned and it's not clear to me what risk this poor test coverage is posing to our customers.
I'm looking for testimonials from customers that use PBM, including which facets they create policies for, which properties are most commonly used for conditions, and which condition operators are most used.
For example, I just learned that PBM supports running a WQL WMI query as a condition operator. How many customers are relying on such WQL queries in their PBM deployments?
Based on this feedback I will adjust our test coverage accordingly.

Your input is greatly appreciated!

[marcingminski]

I used to use it a lot when I was a DBA and was a big advocate. PBM allowed me to manage hundreds and thousands of servers with no issue. I even had a go at improved reporting: https://github.com/marcingminski/sql-policy-management.
I would have to refresh my memory about the pros and cons but overall, the interface was a bit clunky and wasn't easy to update/deploy/remove policies across multiple instances. I ended up keeping policies (XML) on disk and using PowerShell for evaluation. I can't remember the exact reasons but the end result was there was no prevention, only evaluation and post-evaluation enforcement via PowerShell (obviously as SQL was not aware of the policy until evaluation)

[tgrohser]

in the 27 years I use SQL Server (in several large enterprises in various industries) I remember trying to use it once but since with many of the build in solutions it did not cover all use cases and had no way to create policies that understood HA/DR and Scale out we used different ways of handling desired configuration management. So long story short: Never used in in production.

[shueybubbles]

I got one answer through another channel - customer support!
The customer is trying to block creation of logins that use SQL authentication where PasswordPolicyEnforced is false. Attempting to scope the target set using a condition on LoginType=SqlLogin doesn't seem to work; password policy condition is being applied to all logins. I'm investigating the root cause.

[shueybubbles]

I believe the problem is that LoginType is only available from the Login facet, not from Login Options. Only Login Options facet can be used for the DDL-blocking policies. We'd have to add LoginType to ILoginOptions in a SQL Server CU.

[shueybubbles]

Another potential blocker for policy adoption in newer versions of SQL server is the set of events that can be processed by the DDL trigger PBM uses has not kept up with the evolution of SQL Server. I don't think the events represent the full range of object types that customers may want to create policies for.
Are there other object types or events that customers need to create policies for?

This is the full list in SQL Server 2019:

event_name	target_type
ALTER_APPLICATION_ROLE	APPLICATION ROLE
ALTER_ASYMMETRIC_KEY	ASYMMETRICKEY
ALTER_AUTHORIZATION_DATABASE	ASYMMETRICKEY
ALTER_AUTHORIZATION_DATABASE	DATABASE
ALTER_AUTHORIZATION_DATABASE	FUNCTION
ALTER_AUTHORIZATION_DATABASE	PROCEDURE
ALTER_AUTHORIZATION_DATABASE	ROLE
ALTER_AUTHORIZATION_DATABASE	SCHEMA
ALTER_AUTHORIZATION_DATABASE	SEARCHPROPERTYLIST
ALTER_AUTHORIZATION_DATABASE	SEQUENCE
ALTER_AUTHORIZATION_DATABASE	TABLE
ALTER_AUTHORIZATION_DATABASE	VIEW
ALTER_AUTHORIZATION_SERVER	ENDPOINT
ALTER_AUTHORIZATION_SERVER	SERVER ROLE
ALTER_COLUMN_ENCRYPTION_KEY	COLUMN_ENCRYPTION_KEY
ALTER_DATABASE	DATABASE
ALTER_ENDPOINT	ENDPOINT
ALTER_ENDPOINT	SERVER
ALTER_FUNCTION	FUNCTION
ALTER_LOGIN	LOGIN
ALTER_PROCEDURE	PROCEDURE
ALTER_RESOURCE_POOL	RESOURCEPOOL
ALTER_ROLE	ROLE
ALTER_SCHEMA	FUNCTION
ALTER_SCHEMA	PROCEDURE
ALTER_SCHEMA	SCHEMA
ALTER_SCHEMA	SEQUENCE
ALTER_SCHEMA	SYNONYM
ALTER_SCHEMA	TABLE
ALTER_SCHEMA	TYPE
ALTER_SCHEMA	VIEW
ALTER_SCHEMA	XMLSCHEMACOLLECTION
ALTER_SEARCH_PROPERTY_LIST	SEARCHPROPERTYLIST
ALTER_SEQUENCE	SEQUENCE
ALTER_SERVER_ROLE	SERVER ROLE
ALTER_TABLE	TABLE
ALTER_USER	USER
ALTER_VIEW	VIEW
ALTER_WORKLOAD_GROUP	WORKLOADGROUP
ALTER_XML_SCHEMA_COLLECTION	XMLSCHEMACOLLECTION
AUDIT_SERVER_OPERATION_EVENT	SERVER
CREATE_APPLICATION_ROLE	APPLICATION ROLE
CREATE_ASYMMETRIC_KEY	ASYMMETRICKEY
CREATE_COLUMN_ENCRYPTION_KEY	COLUMN_ENCRYPTION_KEY
CREATE_COLUMN_MASTER_KEY	COLUMN_MASTER_KEY
CREATE_DATABASE	DATABASE
CREATE_ENDPOINT	ENDPOINT
CREATE_ENDPOINT	SERVER
CREATE_FUNCTION	FUNCTION
CREATE_LOGIN	LOGIN
CREATE_PROCEDURE	PROCEDURE
CREATE_RESOURCE_POOL	RESOURCEPOOL
CREATE_ROLE	ROLE
CREATE_SCHEMA	SCHEMA
CREATE_SEARCH_PROPERTY_LIST	SEARCHPROPERTYLIST
CREATE_SEQUENCE	SEQUENCE
CREATE_SERVER_ROLE	SERVER ROLE
CREATE_SYNONYM	SYNONYM
CREATE_TABLE	TABLE
CREATE_TYPE	TYPE
CREATE_USER	USER
CREATE_VIEW	VIEW
CREATE_WORKLOAD_GROUP	WORKLOADGROUP
CREATE_XML_SCHEMA_COLLECTION	XMLSCHEMACOLLECTION
DROP_ENDPOINT	SERVER
RENAME	FUNCTION
RENAME	PROCEDURE
RENAME	SEQUENCE
RENAME	TABLE
RENAME	TYPE
RENAME	VIEW
RENAME	XMLSCHEMACOLLECTION
SAC_ENDPOINT_CHANGE	SERVER