Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


2
0
Sign in
to vote
Type: Bug
ID: 800598
Opened: 9/13/2013 6:58:12 AM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

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.
Details
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
Sign in to post a workaround.
Posted by Jon Gurgul on 9/13/2013 at 9:51 AM
Field:
ExecuteSql('String',
                    Concatenate(    
                                     Concatenate('
                                     SELECT d.[name] FROM sys.server_principals sp
                                     INNER JOIN sys.databases d
                                     ON sp.[default_database_name] = d.[name]
                                     AND d.[state_desc] = ''ONLINE''
                                     AND d.[name] NOT LIKE ''ReportServer%''
                                     AND d.[database_id] NOT IN (1,3,4)
                                     WHERE sp.[name] = '''
                                    ,@Name)
                                 , '''')
)

Value:
@DefaultDatabase
File Name Submitted By Submitted On File Size  
9-13-2013 2-09-32 PM.png 9/13/2013 7 KB
ConnectItemCode.sql 9/21/2013 6 KB
ConnectItemCode.sql 9/23/2013 6 KB