Why are objects missing from the Security Resource Descriptions (SY09400) table?

If you want to do any reporting on Microsoft Dynamics GP application level security from the SQL Server level, you need to use the Security Resource Descriptions (SY09400) table to map the Resources Type, Dictionary ID and Security/Resource ID to actual named resources that make sense to humans.

I have often seen questions raised about why some systems have all the resources listed in this table and other systems only have a subset of the resources or the table is empty.

The Security Resource Descriptions (SY09400) syCurrentResources table is used because the actual security resource information is stored in the Dexterity Dictionaries that make up Microsoft Dynamics GP and is only available using Dexterity while the product is running. By populating the data into a SQL table, it makes it available to be used from SQL without needing Dexterity.

Standard Behaviour

The standard behaviour for this table is that it is empty until its contents are populated by the user. To populate the table, you will need to follow the steps below:

1. From the menus select Microsoft Dynamics GP >> Maintenance >> Clear Data.
2. Change the Series to System.
3. From the window’s Display Menu, select Physical.
4. Find and select the Security Resource Descriptions table, then click Insert.
5. Click OK and select Yes in the confirmation Dialog.
6. Select the report destination as Screen and Click OK.
7. Wait while the table’s contents are cleared and then recreated.
8. Close the report window when it is displayed.

The table will be populated by Microsoft Dynamics GP with the following resource types:

• 2: Forms (Windows)
• 23: Reports
• 21: Service Procedures* (GP 2015 or later)
• 57: OData Stored Procedures (GP 2016 or later)
• 58: OData Views

* Due to a bug, only Service Procedures for the last product in the Dynamics.set are added.

On my GP 2016 demo system this resulted in 3,785 records in the table. However, this is not a complete list of resources and you will get lots of missing data if you JOIN to the table as it is. Or, if you use an OUTER JOIN, you will get lots of NULL results returned.

Enhanced Behaviour

So, how do you get the rest of the missing records into the table? … GP Power Tools is the answer.

GP Power Tools (and its predecessor, the Support Debugging Tool) adds the missing records into the Security Resource Descriptions table so that it contains all possible security records. The following additional resource types will be added:

• 1: Tables
• 21: Service Procedures* (GP 2015 or later)
• 400: Document Access
• 500: Customization Tools
• 600: Series Posting Permissions
• 700: Import Tools
• 800: Letters
• 900: Navigation Lists
• 1000: SmartList Objects
• SmartList Builder (if installed)
• Extender (if installed)

* GP Power Tools adds Service Procedures for all products, fixing bug in Dynamics GP.

On my GP 2016 demo system this brought the number of records to 5,625, that’s an additional 1,840 records.

GP Power Tools also does not need you to manually perform the Clear Data step as it will automatically check for and update the table (if needed) when the Security Information or Security Analyzer windows are opened. If you want to force a refresh of the table, you can also use the Options menu on either of these windows and select Refresh Resource Information Table.

Please note that if you do have GP Power Tools installed, the Security Information, Security Information Resources and Security Analyzer windows can provide many views into the Microsoft Dynamics GP application level security data (including taking Deny Based Security data into account). You should be able to answer any question about security without needing to work at the SQL level by using these windows.

For more information, look at the GP Power Tools portal: http://WinthropDC.com/GPPT

Hope this is helpful.

David

This article was originally posted on http://www.winthropdc.com/blog.