Thursday, December 24, 2009

SmartList Builder and SQL Tables/Views

I am sure many of you already are aware of this, but I ran across a bit of an issue this week with what I thought should be a simple deployment of a SmartList Builder item. Here are the steps I went through....
  1. Created two views in SQL based on a combination of GP and custom tables
  2. Granted access to the views for SmartList Builder, Tools>>SmartList Builder>>Security>>SQL Table Security
  3. Created my Smartlist, Tools>>Smartlist Builder>>Smartlist Builder
  4. Tested by Smartlist, all looked good
  5. Deployed views to client site
  6. Exported Smartlist Builder to XML
  7. Imported to client site
  8. Granted access to the views for SmartList Builder at the site
  9. Error when viewing Smartlist, "You do not have security privileges to view all of the tables used in this SmartList"
  10. Follow steps in KB Article #967731 to configure a Security Task and Security Role that includes the operation Smarlist Builder\Smartlist Builder Permissions\Smartlist Builder\View Smartlists with SQL Tables
  11. Still get error
  12. Grant DYNGRP SELECT Permissions on both views
  13. Still get error
  14. Run Grant.SQL
  15. Still get error

Ugh. And the error happened for both regular users and sa. So what was the issue? Well, you have to have the permissions set BEFORE you import the XML file. So make sure you have those steps completed before you do your import, I did not narrow it down but I would think steps 8, 12, and 14 would be important. But I would do 8, 10, 12, and 14 just to be safe.

Once I had the permissions set, and then imported the XML file, all was good :) It seems that it does not recognize the permissions fully if they are set afterwards.

Happy holidays to all!

at

5 comments:

  1. UnknownJuly 20, 2012 at 6:24 AM

    Christina,
    Sure the response is well overdue. I was experiencing the same issue and did the following to resolve it. In Dynamics GP 2010 R2 there is an option in Security Tasks for the following:
    Product: Smartlist Builder
    Type: Smartlist Builder Permissions
    Series: Smartlist Builder

    The option displayed is View Smartlists with SQL Tables. Once this is turned on for the security task, make sure the task is assigned to your users security role and give it a try.

    Hopefully the 3 years it took for a response wasn't to long :) Thought I would post it anyway in case others stumble on the post.

    ReplyDelete
  2. Christina PhillipsAugust 9, 2012 at 7:31 AM

    Hey Sean, yeah the KB I referenced addresses that. The issue is with the order of importing and defining security in order for it to work (if recollection serves). :)

    ReplyDelete
  3. UnknownAugust 26, 2014 at 10:31 AM

    Christina, What did you do after you had already imported. I have deleted the Smartlist in SLB and set all of the security and then I imported the SL again. I am still getting the error. Any help would be appreciated. Lisa

    ReplyDelete
  4. Christina PhillipsSeptember 10, 2014 at 1:29 PM

    Hi Lisa- When you deleted it, did you open up SmartList so the change was included?

    ReplyDelete
  5. Sergio526September 30, 2014 at 1:52 PM

    We had this happen with a customer as well, but nothing we found was working. Luckily, the very talented consultant who was working on the issue noticed in the exported SL's xml was a database ID (TWO) that the customer didn't have in their system. She changed the ID right in the xml to one of their company IDs before re-importing and bingo, the thing finally worked!

    ReplyDelete