Robert Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in GP 10 SQL script that includes SmartList objects. I have tested this script with both GP 10.0 and GP 2010 and for both versions to get the most out of this 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 http://victoriayudin.com/gp-reports/
Shows all security roles, tasks and detailed resource descriptions
     including SmartList by user by company for GP 10.0 and GP 2010
*******************************************************************/

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.


Filed under: Dynamics GP, GP 10.0, GP 2010, GP SQL scripts Tagged: Dynamics GP, GP 10.0, GP 2010, GP Reports, GP SQL view, security, SQL code