I am trying to create a policy that will test that every Login has a default database that is either TempDB or an online user database. To do this I have created a specific condition that is based on the Login facet. I have added a single Expression based on the @DefaultDatabase Field. I chose the NOT IN Opertator and in the value I have tried adding the following code via the Advanced Edit UI;
Array(ExecuteSql('String', 'SELECT STUFF((SELECT '' ,'' + QUOTENAME(s.name, '''''''') FROM master.sys.databases AS s WHERE (database_id > 4 OR name = ''tempdb'') AND state_desc = ''ONLINE'' AND name not like ''ReportServer%'' FOR XML PATH('''')), 1, 2, '''');'))
This uses the ExecuteSQL function to generate a list of appropriate database names and then the Array function to present this list to the condition for evaluation.
Somewhere this doesnt work as I am seeing false positives. That is, a Login has a default database that meets my requirements but is shown as failing this policy. The policy error detail also contradicts itself as it shows the database name array has the name that should have been matched for the login - see attached image.
If I add a static list of values to the Array function (ie Array(tempdb, DB1, DB2, DB3) ) the evaluation works as expected.