David Meego

From the Microsoft Dynamics GP Application Level Security Series.

In the previous post, How to identify the Security Tasks and Security Roles associated with a specific window or report we used the syCurrentResources (SY09400) table to help us identify resources.

We can also use the Resource Information feature of the Support Debugging Tool for Microsoft Dynamics GP to translate a form's Display Name to a Dictionary ID and Resource ID.  As we are also going to use the SQL Execute feature to run our query, we will be using Advanced Mode and so need to log in as a user with administrator permissions.

Below are the steps, please skip steps already completed previously: 

  1. Click here for the links to download the tool from PartnerSource.
     
  2. Extract the archive files to your application folder.
     
  3. Launch Microsoft Dynamics GP and select Yes to include new code.
     
  4. Log into Microsoft Dynamics GP as 'sa' or a user with administrator permissions.
     
  5. From the menus click Microsoft Dynamics GP, click Tools, click Support Debugging Tool.
     
  6. From the Support Debugging Tool window, click Options, click Dex.ini Settings.
     
  7. On the Debug Tab, in the Support Debugging Tool Options section, select the Enable Debugger Advanced Mode Features checkbox.  If a System Password is in use, you will need to enter it.
     
  8. Click OK to close the Dex.ini Settings window.
     
  9. From the Support Debugging Tool window, click Options, click Resource Information.
     
  10. On Resource Information window enter the Display Name and the first form which has that name will be displayed.
     
  11. Make a note of the Product ID and Resource ID values.
     
  12. From the Support Debugging Tool window, click Options, click SQL Execute. If a System Password is in use, you will need to enter it. 
     
  13. Copy the following query into the script window, update the Product ID and Resource ID and click Execute. Screenshot below:

SQL Execute 

SQL Query

SELECT  ISNULL(A.SECURITYROLEID,'') AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,'') AS SECURITYROLENAME, --ISNULL(M.SECURITYROLEDESC,'') AS SECURITYROLEDESC,
 ISNULL(O.SECURITYTASKID,'') AS SECURITYTASKID, ISNULL(T.SECURITYTASKNAME,'') AS SECURITYTASKNAME, --ISNULL(T.SECURITYTASKDESC,'') AS SECURITYTASKDESC,
 O.DICTID, O.SECRESTYPE, O.SECURITYID
FROM DYNAMICS.dbo.SY10700 O
FULL JOIN DYNAMICS.dbo.SY09000 T ON T.SECURITYTASKID = O.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY10600 A ON A.SECURITYTASKID = T.SECURITYTASKID
FULL JOIN DYNAMICS.dbo.SY09100 M ON M.SECURITYROLEID = A.SECURITYROLEID
WHERE O.DICTID = <Product_ID> AND O.SECURITYID = <Resource_ID> AND O.SECRESTYPE = 2

Note: The <Product_ID> and <Resource_ID> placeholders represent the Product ID and Resource ID from the Resource Information window. For example, "Sales Transaction Entry" is Product ID = 0, Resource ID = 619.

Below are the example results based on a default installation for 'Sales Transaction Entry':

Result Set
SECURITYROLEID          SECURITYROLENAME                SECURITYTASKID          SECURITYTASKNAME        DICTID  SECRESTYPE      SECURITYID
----------------------- ------------------------------- ----------------------- ----------------------- ------- --------------- ----------
BOOKKEEPER*             Bookkeeper                      TRX_SALES_001*          Enter SOP transactions  0       2               619
CUSTOMER SERVICE REP*   Customer Service Representative TRX_SALES_001*          Enter SOP transactions  0       2               619
OPERATIONS MANAGER*     Operations Manager              TRX_SALES_001*          Enter SOP transactions  0       2               619
SHIPPING AND RECEIVING* Shipping and Receiving          TRX_SALES_001*          Enter SOP transactions  0       2               619

If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.

To speed up the process, a settings file with the SQL Execute Query saved has has been attached to the bottom of this post which can be loaded using the Configuration Export/Import window.

David