Everyone is familiar with users in Microsoft Dynamics GP getting permissions errors when attempting to access a window or a report, or logging in or other processes.

To determine what the user needs access to in order to resolve these type of permission errors, it helps to know what security role/security task combinations give a user access to the object in question, as well as what the object in question is.

To do this, you can use the following steps (after verifying you have recent, valid backups of all GP databases) :

1) First, we want to populate the SY09400 table, which is the Security Resource Descriptions table, held in the DYNAMICS/system database.

    a. Login to Microsoft Dynamics GP as 'sa' or another PowerUser, then click on Microsoft Dynamics GP, then Maintenance and then Clear Data.

    b. In the Clear Data window, on the Display menu, click Physical.

    c. In the Series list, choose System.

    d. In the list of tables that populate, find and click on the Security Resource Descriptions table, then click Insert.

    e. Click OK, then click Yes.

    f. In the Report Destination window that opens, choose to send the report to Screen, then click OK. Close the report once the processing is finished.

    At this point, the SY09400 system table will be populated with a list of forms/windows (secrestype 2), reports (secrestype 23), views (secrestype 58) and stored procedures (secrestype 57).

2) Next, we want to capture a dexsql.log of the permissions error occurring.

    a. Preferably on an instance of Microsoft Dynamics GP that no other users are actively using, browse to the Dex.ini file which by default is located at the C:\Program Files (x86)\Microsoft Dynamics\GP####\Data\ directory and locate the following statements in the Dex.ini file:

             SQLLogSQLStmt=FALSE

             SQLLogODBCMessages=FALSE

             SQLLogAllODBCMessages=FALSE 

       Change these statements to TRUE, as follows:

              SQLLogSQLStmt=TRUE

             SQLLogODBCMessages=TRUE

             SQLLogAllODBCMessages=TRUE

        Save changes to the Dex.ini and close the file.

    b. Login to Microsoft Dynamics GP as the user that is experiencing the permissions error and re-create the error.

        **Once the permissions error shows, do not click anything further in Microsoft Dynamics GP.**

        Browse back to the location of the dexsql.log, which will also be the same directory, by default, as the Dex.ini > C:\Program Files (x86)\Microsoft Dynamics\GP####\Data\, and open the existing dexsql.log in a text editor like NotePad.

    c. Usually, searching from the bottom of the dexsql.log, we're looking for a call to the main security view, SY10000, which will look like this:

             { CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'GPUser1', 3, 0, 619, 2 ) }

        In this script example, it gives us the following information:

        -- The user id is 'GPUser1'

        -- The company ID is 3

        -- The product or dictionary ID is 0 (Microsoft Dynamics GP)

        -- The secrestype value is 2 indicating it is a window/form

        -- The security ID of the object is 619.

   d. With this information, we can use the SY09400 table we populated to find out exactly what that object is, via this example script:

              select * from SY09400 where DICTID = 0 and SECRESTYPE = 2 and SECURITYID = 619

       Running this script, it shows us that the DSPLNAME is the 'Sales Transaction Entry' window.

   e. Finally, running a script such as this example against the GP system database, we can get the security role / security task combinations that would give users access to, in this case, the Sales Transaction Entry window:

          Select c.SECURITYROLEID, b.SECURITYTASKID, a.DSPLNAME, a.PRODNAME, a.TYPESTR, a.SECURITYID, a.RESTECHNAME, a.DICTID, a.secrestype, a.securityid 

           from SY09400 a
           full join SY10700 b
           ON a.DICTID = b.DICTID and a.secrestype = b.secrestype and a.securityid = b.securityid
            full join SY10600 c
           ON c.securitytaskid = b.securitytaskid
           Where a.DSPLNAME = 'Sales Transaction Entry'

 

     This script will give you information needed such as this:

     

     Now you can look at what security roles are assigned to the user receiving the permissions error and whether they need additional roles assigned to them or an security task. The SY10500 system table holds a list of security roles assigned to each user, including PowerUsers, per company database.

The SECRESTYPE values are:

1 = Files
2 = Windows
23 = Reports
57 = Stored Procedures
58 = Views
400 = Document Access
500 = Customization Tools
600 = Series Posting Permissions
700 = GP Import
800 = Letters
900 = Navigation Lists
1000 = SmartList Objects

Once the SY09400 table is populated and you have the zDP_SY10000SS_1 call from the dexsql.log, using the above script example to plug in the object the user doesn't have access to will help speed up verifying whether the user should have the roles/tasks assigned to them or not, in order to more quickly resolve the permissions error and getting the user back working again.

This information can also be used to determine why users have access to a window or report that they shouldn't have permissions to access.

Hopefully you won't run into too many permissions errors with Microsoft Dynamics GP, but you can put this information in your 'toolkit' and maybe save you a support case down the road.

Thanks....