Open Forum

Expand all | Collapse all

user access

Jump to Best Answer
  • 1.  user access

    Posted Jan 17, 2020 09:13 AM
    Is there a report I can pull via GP or SQL that will show me all Roles/Tasks with a specific operation enabled?



    Thank you

    ------------------------------
    Renee Boucher

    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: user access
    Best Answer

    GPUG ALL STAR
    Posted Jan 17, 2020 09:36 AM
    @Renee Boucher,

    I created a refex (REFreshable EXcel report)​ using @Victoria Yudin's method for extracting Role, Task, and Resource information from GP​. With the information in an Excel workbook, I created a pivot table that allows me to do the very thing you want: filter the Resources to just the one you're interested in to determine which Roles and Tasks contain that Resource.

    Here's the SQL query that forms the foundation of my refex. I created a View with the name vwvy_Security_and_SL_Details based on Ms. Yudin's article:

    SELECT v.[User_ID]
        , v.Company_ID
        , v.Company_Name
        , v.Security_Role_ID
        , v.Security_Task_ID
        , v.Security_Task_Name
        , v.Security_Task_Description
        , v.Dictionary_ID
        , v.Product_Name
        , v.Resource_Type
        , v.Resource_Display_Name
        , v.Resource_Technical_Name
        , v.Resource_Series
    FROM dbo.vwvy_Security_and_SL_Details AS v;

    If you have @David Musgrave's excellent GP Power Tools, you can find out what you want from within GP.​

    Does this help?

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: user access

    GPUG ALL STAR
    Posted Jan 17, 2020 10:57 AM
    Who do you think worked with Robert Cavill to create that query on Victoria's site in the first place. :) It is just a shame that Robert doesn't work in the GP/software world anymore.

    Also without GP Power Tools, the contents of the SY09400 resource descriptions table will be incomplete. Core Dynamics GP does not add all resources to this table. GP Power Tools adds the missing records.

    But GP Power Tools also has the powerful Security Information window which provides multiple views into the security data and allows you to dynamically change the views and explore the security data as needed.

    I suggest that all partners and customers review the benefits presentation so you can see why every GP customer globally should have GP Power Tools: https://www.winthropdc.com/ftp/pub/GPPowerTools_Benefits.ppsx

    You can also watch the demonstration videos at https://winthropdc.wordpress.com/gp-power-tools-portal/gp-power-tools-videos/

    Regards

    David


    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: user access

    GPUG ALL STAR
    Posted Jan 17, 2020 11:11 AM
    Isn't it about your bed time, @David Musgrave? (10:08 AM Central is 12:08 AM tomorrow in Perth)

    No, I didn't know that you were involved with that query. ​It works champion. And, yes, I did know that GPPT fills in the gaps in the SY09400 table.

    All the more reason to have GPPT in one's arsenal!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: user access

    GPUG ALL STAR
    Posted Jan 17, 2020 11:18 AM

    Hi @Steve Erbach

    I wrote the first version of that query back in November 2008, which was also added to KB 951229

    https://winthropdc.wordpress.com/2008/11/11/how-to-identify-the-security-tasks-and-security-roles-associated-with-a-specific-window-or-report/

    Similar queries underlie how GP Power Tools gets its data for the Security Information window.

    Regards

    David



    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Conference-GPUG_200x200


  • 6.  RE: user access

    Posted Jan 17, 2020 03:26 PM
    Hi Steve,

    Thank you so very much, this is extremely helpful however it doesn't give the specific operations on the task. Our concern is that tasks may have been created with the "Purchase Order Entry" operation selected/enabled mistakenly. We need to somehow find and correct.

    Thank you

    ------------------------------
    Renee Boucher
    Data System Services
    Sterling Heights MI
    ------------------------------

    Conference-GPUG_200x200


  • 7.  RE: user access

    GPUG ALL STAR
    Posted Jan 17, 2020 03:55 PM
    @Renee Boucher,

    As @David Musgrave pointed out, using the GP capability to fill the SY09400 table with all Resources doesn't quite get them all. GP Power Tools does. That may be the issue with the refex option I proposed to you.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List as of 12-Nov-2019
    ------------------------------

    Conference-GPUG_200x200


  • 8.  RE: user access

    GPUG ALL STAR
    Posted 29 days ago
    @Renee Boucher

    GP Power Tools offers a free 30 day trial period.  Why don't you install and test out the reporting capabilities it has. Check out the benefits presentation, link was in an earlier reply.

    Best case scenario, You end up purchasing a product that every GP site globally should have installed.

    Worst case scenario, the SY09400 table will be populated for you.

    Regards

    David​

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Conference-GPUG_200x200


  • 9.  RE: user access

    GPUG ALL STAR
    Posted 29 days ago
    Edited by Beat Bucher 29 days ago
    Hi @Renee Boucher,
    If you happened to own SmartList Builder, eOne has some free SL templates about security that you can download from their web site directly into SL builder by using the import feature.. There is a whole collection of reports about GP security.. and more.
    I agree with David & Steve, this might not populate the SY09400 table and effectively the easiest way of doing so is to install the GPPT for a 30 days trial.. you're gong to love the product and might wonder why you haven't done it before :-)​.. this tool is so effective that it should be purchased by default with every GP instance.. if there is one tool that a company should buy, than it's GPPT.
    As a GP consultant it always makes me cringe when I have to support a customer who doesn't have it installed, as it makes everything so much more difficult and complicate.

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


  • 10.  RE: user access

    Posted 26 days ago
    I use a combo of GP Power Tools and FastPath GRC studio to get the data I need.  There are some items such as SmartLists that do not appear in the tables without doing what David's GP Power Tools.  FastPath has a report on the that table and I did an edit to show the data that GP Power Tools adds such as those SmartLists.  And with FastPath GRC you can publish reports on a set schedule showing who specifically has any combination of security items.

    But out of the box GP, the best is to run the​Security Task Setup to find the tasks that contain the window you are concerned with.  Then you have to run the Security Task Assignment report to find the users with the task assigned to them.  That is why you need the script on Victoria's web site, taking it a step further and getting GP Power Tools, or look into FastPath GRC.

    ------------------------------
    Bill Jones
    Systems Engineer, Financial Systems
    Universal Service Administrative Company
    Washington DC
    ------------------------------

    Conference-GPUG_200x200


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