PBM: Facet Property IN Array doesnt work when array values are generated by ExecuteSQL - by FatherJack

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 800598 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 9/13/2013 6:58:12 AM
Access Restriction Public

Description

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.
Sign in to post a comment.
Posted by FatherJack on 9/30/2013 at 11:49 AM
How do I reactivate this? I added the code that was requested.
Posted by Microsoft on 9/21/2013 at 8:49 PM
Hello,
Please reactivate this issue when you could get us the script to create the policy and condition

Thanks
Sethu Srinivasan [MSFT]
SQL Server Development Team
Posted by Microsoft on 9/20/2013 at 8:34 AM
Hello,
We are waiting for you to provide us the policy and condition script so that we can work on investigating this issue.

Also, let us know if you have tried the workaround posted for this issue.

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by Sethu Srinivasan on 9/18/2013 at 10:17 AM
Hello,
We are waiting for you to provide us the policy and condition script so that we can work on investigating this issue.

Also, let us know if you have tried the workaround posted for this issue.

Thanks
Sethu Srinivasan [MSFT]
SQL Server
Posted by Sethu Srinivasan on 9/17/2013 at 12:42 PM
Hello,
Thanks for reporting this issue. Can you please script out the policy and condition using SSMS and attach the script along with this feedback?


Thanks
Sethu Srinivasan [MSFT]
SQL Server