SQL view with security and SmartList details in GP


Robert Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in Dynamics GP SQL script that includes SmartList objects. This script was originally written for GP 10.0 and GP 2010, but will also work with GP 2013 and GP 2015. To get the most out of this script you will want to populate the GP system resource table by following the steps below:

  1. Go to Microsoft Dynamics GP > Maintenance > Clear Data
  2. Click Display on the toolbar and choose Physical
  3. Select System under Series
  4. Click Security Resource Descriptions under Tables to highlight it and click Insert to add it to the Selected Tables list
  5. Click OK, then Yes to the pop up message asking you if you’re sure that you want to clear data from the table
  6. Send the report to the screen, it should report back with “No errors found”

The view will still work without going through the steps above, but all the resource details will be blank. Please note that the script below should be run against your DYNAMICS database.

~~~~~

CREATE VIEW view_Security_and_SL_Details
AS
/*******************************************************************
view_Security_and_SL_Details
Create this in the DYNAMICS database
Created May 13 2010 by Victoria Yudin - Flexible Solutions, Inc.
     and Robert Cavill - Emeco
For updates see https://victoriayudin.com/gp-reports/
Shows all security roles, tasks and detailed resource descriptions
     including SmartList by user by company for Dynamics GP
*******************************************************************/

SELECT DISTINCT
   S.USERID [User_ID],
   S.CMPANYID Company_ID,
   C.CMPNYNAM Company_Name,
   S.SECURITYROLEID Security_Role_ID,
   coalesce(T.SECURITYTASKID,'') Security_Task_ID,
   coalesce(TM.SECURITYTASKNAME,'') Security_Task_Name,
   coalesce(TM.SECURITYTASKDESC,'') Security_Task_Description,
   coalesce(R.DICTID,SO.ASI_DICTID,'') Dictionary_ID,
   coalesce(R.PRODNAME,'') Product_Name,
   coalesce(R.TYPESTR,SO.ResType,'') Resource_Type,
   coalesce(R.DSPLNAME,SO.SmartlistObject,'') Resource_Display_Name,
   coalesce(R.RESTECHNAME,'') Resource_Technical_Name,
   coalesce(R.Series_Name,'') Resource_Series

FROM SY10500 S   -- security assignment user role

LEFT OUTER JOIN
   SY01500 C   -- company master
   ON S.CMPANYID = C.CMPANYID

LEFT OUTER JOIN
   SY10600 T  -- tasks in roles
   ON S.SECURITYROLEID = T.SECURITYROLEID 

LEFT OUTER JOIN
   SY09000 TM  -- tasks master
   ON T.SECURITYTASKID = TM.SECURITYTASKID 

LEFT OUTER JOIN
   SY10700 O  -- operations in tasks
   ON T.SECURITYTASKID = O.SECURITYTASKID 

LEFT OUTER JOIN
   SY09400 R  -- resource descriptions
   ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE
   AND O.SECURITYID = R.SECURITYID 

LEFT OUTER JOIN  -- smartlist objects
   (SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE,
 	ASI_DICTID, SL_OBJID, SmartlistObject,
	'Smartlist' ResType
   FROM
      (SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE,
              SECURITYID / 65536 ASI_DICTID, SECURITYID % 65536 SL_OBJID
      FROM SY10700
      WHERE SECRESTYPE = 1000 AND DICTID = 1493) ST
   JOIN
      (SELECT coalesce(TRANSVAL, ASI_Favorite_Name) SmartlistObject,
              ASI_Favorite_Dict_ID, ASI_Favorite_Type
      FROM ASIEXP81  F
      LEFT JOIN
         ASITAB30 A
         ON F.ASI_Favorite_Name = A.UNTRSVAL
         AND A.Language_ID = 0
         WHERE ASI_Favorite_Save_Level = 0) SM
      ON ASI_DICTID = ASI_Favorite_Dict_ID
      AND SL_OBJID = ASI_Favorite_Type) SO
   ON SO.DICTID = O.DICTID AND O.SECRESTYPE = SO.SECRESTYPE
   AND O.SECURITYID = SO.SECURITYID

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Security_and_SL_Details TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Thanks again to Robert for his work on this! For more SQL code and help with reporting on Dynamics GP data, please take a look at my GP Reports page.

24 Responses to “SQL view with security and SmartList details in GP”

  1. Thank you! It is very Helpful!!! 🙂

    Like

  2. Hi Victoria,

    I found that making the following changes to this will help produce more accurate results:

    Convert the “R.DICTID” value to varchar to ensure a blank value is populated instead “0”

    coalesce(CONVERT(varchar(25),R.DICTID),SO.ASI_DICTID,”) Dictionary_ID,

    Change the fixed text for the ResType from “Smartlist” to “SmartList Objects” to ensure the column values are uniform

    ‘SmartList Objects’ ResType

    Thanks,

    Nick

    Like

  3. jeffrowleskianoff Reply May 11, 2012 at 7:06 am

    Hi Victoria,
    I posted a request on Partner GP Forums to ask the community how to create a Security SQL view to show which smartlists each user can access…and your friend Mariano Gomez referred me to your super-duper article…This is more than I could have asked for…it’s so wonderfully inclusive…so hats-off to Robert and yourself!

    Only issue I’m having is when attempting to add this view as a Smartlist Builder Object. I regularly create SQL views for GP “Company” databases and add them as Smartlist Builder Objects. However, I have not attempted that with a “DYNAMICS” database SQL view until now. I’m logged in as ‘sa’, DYNGRP permissions are set to the SQL view, and there is a security task for smartlist builder permissions allowing me to ‘create Smartlists with SQL tables’ and ‘View Smartlists with SQL Tables. However, when I go to Smartlist Builder SQL Table Security and mark DYNAMICS as the database to use, nothing appears for Tables and Views…it’s completely grayed out? I’ve tried this on my test machine and on a client with same results. Am I missing something?

    Thanks again,
    Jeff Rowles
    L. Kianoff & Associates

    Like

    • Hi Jeff,

      Is the checkbox to the left of the DYNAMICS database checked? If not, that would cause what you are seeing.

      -Victoria

      Like

      • jeffrowleskianoff Reply May 11, 2012 at 7:40 am

        Thanks for the quick reply Victoria…yes it is.

        Like

        • Jeff,

          That’s very strange. I know it works, because we have several SmartLists based on views in the DYNAMICS db and they are working just fine. I just checked on our GP 2010 R2 install and I see all those options when logged in as my regular GP user who is a POWERUSER. Can you try that – logging in as a user with POWERUSER rights – to see if that makes any difference?

          -Victoria

          Like

          • jeffrowleskianoff Reply May 11, 2012 at 8:30 am

            I was originally logged in with my user account that has Poweruser rights, also tried it with ‘sa’ and experienced same result. But after going back and forth and trying several things, I figured out what was happening. If I just clicked on the DYNAMICS checkmark nothing would show…but I was not also clicking directly on the database name ‘DYNAMICS’…once I did this, the view appeared!

            Thanks again for your help!
            Jeff

            Like

  4. Victoria,

    This is great, thank you for sharing so much with us. When I look at the view I see several records where the Resource Type is blank. What does that mean?

    Leslie

    Like

    • Hi Leslie,

      Great question! I did a bunch of testing on this and what I am seeing is that there are a number of operations in the SY10700 table that do not have resource details in the SY09400 table. You can see this by running the following against the DYNAMICS database:

      SELECT O.*, R.*
      FROM SY10700 O
      LEFT OUTER JOIN SY09400 R
      ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE
      AND O.SECURITYID = R.SECURITYID

      I am doing left joins to make sure as much data as possible is captured in the view and showing any NULLs as blank so if this is used in SmartList it doesn’t return garbled results (not sure if this is still an issue in GP 2010, but it was in GP 10.0).

      From the results it looks like anything with a SECRESTYPE of 1, 600,900 or 1000 does not have corresponding details in the SY09400 table and thus no resource descriptions in the view. SECRESTYPE of 1000 is SmartList, so that is added into the view separately, but I don’t know what the others are. I will try to investigate this and will report back if I find anything, but if you have any resources for this information, please let me know.

      Thanks!
      -Victoria

      Like

      • Hi Victoria,

        SECRESTYPE of 1 = Tables, 600 =Series Posting Permissions, 900 = Navigation Lists. The omission of the Series Posting Permissions I believe is worth noting. I am working on a tool that will include all of these resources descriptions, when it is ready for prime time I will let you know.

        Cheers!
        Terry Blaser
        Premier Field Engineer
        Microsoft Services

        Like

        • Hi Terry,

          Thank you very much for the info! Looking forward to checking out the tool you’re working on.

          -Victoria

          Like

          • This one is interesting because I really need to be able to run a report on Series Posting Permissions SECRESTYPE = 600 but have not been able to find the table that stores this information. It’s funny, because it shows up in the report “Security Task Setup Report” as “Series Posting Permissions” ,”General Entry”, “Financial”, Microsoft Dynamics GP” (as an example of someone who has permission to post Journal entries. So that report is looking it up somewhere…… I just wish I could find that table. Maybe I’ll run a SQL Trace.

            Like

  5. Hi Victoria.

    I’ve recently started working with Microsoft Dynamics GP and wanted to “audit” my system to see what users are in the system and what types of security roles each user has. I then want to see if anyone has “incompatible” roles which would allow them to do things that they weren’t supposed to be doing.

    The above script seems to be a life saver if it does what I think it does. Will the output of the above script give me the information that I’m looking for?

    Also, could you explain it a bit further on how to run the above script? Is it just a cut and paste? Do I need to edit the script before running it?

    I look forward to reading your blogs further and look forward to your response and any other advice that you could provide.

    Brett

    Like

    • Hi Brett,

      The script in this post will give you a lot of data, most likely you will need a lot of GP specific knowledge to make sense of it all. If you’re just starting to work with GP, I would recommend working with someone knowledgeable at your GP Partner organization to help you with this project. Some of the security role and task names are very ambiguous, especially if you are not used to GP terminology yet. For all the information that you can find in books or on the web, nothing can compare to the experience of having worked with GP for something like this.

      To answer you question about how to actually use the script – if you have SmartList Builder, you can follow the step in my blog post on How to use a SQL view in SmartList Builder. You should be able to run the script as it is with no changed needed. Also make sure to follow the 6 steps at the beginning of this post, otherwise you will only get partial data returned.

      -Victoria

      Like

      • Thanks for such a quick response Victoria.

        I understand that the security roles and task names may be ambiguous, do you know of a good document that I’d be able to read which would put them into perspective?

        Also, I’m trying to create my own segregation of duties matrix. I want to see which users are assigned which roles and tasks and which may be incompatible with each other. Do you know of an easier way to do this? Does documentation exist somewhere which would make this easier to do?

        Finally, do you have a screen shot of the output from the script that we spoke about in my initial post? I’d love to see what the output looks like to determine if I should give it a go.

        Thanks again.

        Brett

        Like

    • Brett,

      There are a couple of 3rd party products available that I believe try to address your concern. Check out Rockton Software’s Auditor/Security Auditor http://www.rocktonsoftware.com. Also FastPath has a similar product http://gofastpath.com

      Leslie

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view with security and SmartList details in GPSQL view with security and SmartList details in GP - DynamicAccounting.net - April 27, 2016

    […] Yudin has an updated SQL view with security and SmartList details in GP available at her […]

    Like

  2. Administering GP Security - Dynamics GP Support and Services Blog - GP Technical Blogs - Microsoft Dynamics Community - April 28, 2011

    […] is a great SQL View on MPV Victoria Yudin’s blog site called “SQL View with Security and SmartList details in GP”.  Be sure to read her entire post as the SQL View is dependent on the Security Resource […]

    Like

  3. SQL view with security and SmartList details in GP - DynamicAccounting.net - May 14, 2010

    […] Yudin has an updated SQL view with security and SmartList details in GP available at her website. Published: Friday, May 14, 2010, 12:00 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: