Dynamics GP User Security Listing or Matrix

Hello all!

If you have been a consultant or system admin for a company utilizing Dynamics GP, then you have probably gotten the request to produce a listing of Users with all of their permissions and securities.  Therefore, you probably know that this listing is extremely difficult with out-of-the-box reporting or any kind of simple query in SQL.

If you are on GP 2013 or higher or have SmartList Builder as a registered module then you are in luck.  Building a SmartList to export to Excel to create the matrix or listing will take about 15-30 minutes to set up.   If you don't have Designer (GP 2013 and 2015 SmartList capability), then you will need to create a view in SQL.

For the SmartLists with Designer/Builder you will create a custom SmartList with this general configuration:

1. Choose Product: Microsoft Dynamics GP
2. Select Series: System
3. In tables hit the + and add the Users Master table
   1. with User ID as the Key Field
4. While User Master is highlighted, click the + again and select the Security Assignment User Role
   1. Link on the User ID Field
5. While Security assignment User Role is highlighted click + again and select the Security Roles Master
   1. Link on Security Role ID Field
   2. This will give you the detail of the Assigned Roles
6. While the Security Roles Master is highlighted click +again and select Security Assignment Role Task
   1. Link on Security Role ID
   2. This will list each Task ID associated with the Role
7. While Security Assignment Role Task is highlighted click + again and select Security Task Master
   1. Link on Security Task ID
   2. This will list each Tasks actual assigned activities

If you do not have the Designer or Builder, use SQL to create a view that you can save as an .rpt and then open in Excel.

These are the tables you will create the view with:

1. In Dynamics DB:
   1. SY01400 - User Master
   2. SY10500 - Security Assignment User Role
   3. SY09100 - Security Roles Master
   4. SY10600 - Security Assignment Role Task
   5. SY09000 - Security Task Master

It will be up to your preference as to what columns to add and joining methods you would like to use.

Please contact Jo Ann de Ruiter with ACE Microtechnology if you have any questions on this.

jderuiter@acemicrotech.com or ace_gpsupport@acemicrotech.com