Open Forum

Expand all | Collapse all

Granting permissions to a new SmartList

  • 1.  Granting permissions to a new SmartList

    Posted 2 days ago

    I created a new Smartlist based on a new database View but it throws a 'You do not have security privileges to view all of the tables...' error to Users that aren't POWERUSER.
    The steps are below

    I created a new database view [slbVendorsOnePmnt] based on the existing slbVendors view.
    GRANT DELETE, INSERT, SELECT, UPDATE [slbVendorsOnePmnt] TO [DYNGRP]

    In GP signed in as sa...,
    In MicrosoftDynamics GP/Tools/SmartList Builder/ Security/ SQL Table Security, checkmarked the new database view

    Goto SmartList Builder, created a new Smartlist 'VendorOnePmnt' use the above View

    In MicrosoftDynamics GP/Tools/Setup/System/SecurityTasks/, created a new Security Task
    Task ID: SMARTLIST_VENDORONEPMNT
    Product: SmartList
    Type: SmartListObjects
    Series: SmartListObjects
    User Type: Full
    Access List: VendorOnePmnt

    At this point a user with RoleID of POWERUSER can see and run the new SmartList

    However, I want a user not a POWERUSER to run the SmartList. So I went into Security Role Setup, selected Role ID 'Purchasing Inquiry' that's assigned to the User and added the above created Security Task to the Role ID.

    The user can see and select the SmartList but gets a 'You do not have security privileges to view all of the tables used in the Smart List'

    I also created another Security Task
    Task ID: SMARTLIST_VENDOR1PMNT
    Product: SmartList Builder
    Type: SmartList Builder Permissions
    Series: SmartList Builder
    User Type: Full
    Acess List: checkmarked View SmartLists with SQL Tables

    Task ID: SMARTLIST_VENDOR1PMNT was also added to the above RoleID with no difference.

    What am I missing? Is there a specific order to creating and adding a SmartList?​

    thanks again.

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Granting permissions to a new SmartList

    GPUG ALL STAR
    Posted 2 days ago
    Hi @James Patt

    After creating the view in SQL did you run the script:

    Grant Select on YOUR_VIEW_NAME to DYNGRP?

    Basically, the DYNGRP role in SQL is assigned to all GP USER ID's​ and gives them access to run the select statement on it.

    Otherwise, you can check in SmartList Builder security to see if you have marked the tables involved in the View as accessible by SmartList Building

    ------------------------------

    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: Granting permissions to a new SmartList

    Posted 2 days ago
    ​Ran 'GRANT SELECT ON [dbo].[slbVendorsOnePmnt] TO [DYNGRP]' and all db tables used by the View are also check marked in Smart List Builder.
    Still no change

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Granting permissions to a new SmartList

    Posted 2 days ago
    My bad, the Smartlist is now running fine for the user as expected. Maybe re-running the GRANT SELECT did the trick.
    Thanks again

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: Granting permissions to a new SmartList

    TOP CONTRIBUTOR
    Posted 2 days ago
    Hi James-

    I am wondering if there is something weird with the security task. We have many custom SmartList Builder SmartLists using SQL views and everything works great.

    In our security setup, we have a security task that is called "SMARTLIST OBJECTS" that combines selecting all of the custom SmartLists created (like you've done already) and also selecting the "View SmartLists with SQL Tables" in the SmartList Builder security.

    This task is assigned to the roles.

    Maybe try that just to rule out any security task/role issue?

    Thanks.
    Joe


    ------------------------------
    Joseph C. Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: Granting permissions to a new SmartList

    Posted 2 days ago
    I'm all set thanks for everyone's help. What's puzzling is between yesterday and today, nothing was​ changed as far as my above steps are concerned. Yesterday, the SmartList didn't want to work for our one User, today it does. The only difference is re-running the GRANT SELECT TO DYNGRP script

    ------------------------------
    James Patt
    Programmer
    Western Region OTB Corporation
    Batavia NY
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role