Cross-Database Queries & Dynamics GP Smartlist Builder

We encountered a situation with one of our Cleveland clients trying to use Smartlist Builder to create a Smartlist that was based on a SQL view that they had developed. The view was a query that was getting  result sets from multiple tables across multiple databases. We setup the security in GP correctly as stated in the Smartlist Builder manual, however we were not able to even see the new Smartlists listed once we logged in as a user that should have access to them. We discovered that this was because the SQL role “DYNGRP” had not been given “Select” permission on the view. Now we could see the new Smartlists, but there was no data presented and we were given no errors. We determined that this was because the user that we were logged in as was not a user in one of the 2 databases that the query pulled from in SQL, therefore the permissions in SQL were getting hung up and not allowing the data to run.

 

We created  a document as a result of this issue and hopefully it will help you if you come across any similar issues. View our document: Smartlist Builder & Cross DB Queries.

 

We ended up enabling “Cross database ownership chaining” because of the number of users that will need to have access to this information and security was not an issue in this client environment.