Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Saturday, February 7, 2015

Using DEXSQL.LOG to Track Security Issues “Not Privileged to run this report”

How many times have you encountered a security issue with a user clicking on a specific button and an error message pops up such as “Not Privileged to run this report” ! It must have been a hard time for you looking through the various security tasks and roles trying to figure out what’s missing.

Although I have always been a big fan of the support debugging tool, I still like to use other methods such as the “DexSQL.Log”, which will be thoroughly illustrated through this article.

Describing the case

A user is granted an access to enter AR Transactions including receivable transactions, cash receipts and apply sales documents. A new security role has been created including the following predefined security tasks:

  • Default User
  • TRX_SALES_013* | Enter receivable transactions
  • TRX_SALES_015* | Enter customer cash receipts
  • TRX_SALES_016* | Apply sales documents

Secuirty Role - AR Transaction

Now as the user enters AR transactions, they clicked on the “Print button” before posting to check the edit list report, and a security message pops up stating that the user has no privilege to run this report.

 Error Message

     

Solution | DEXSQL.LOG Method

One of the method to track this issue easily and quickly without digging quite too deep in the security tasks and roles, is to create a DEXSql.log which will take you to the specific missing security record for this specific report.

1- Creating DEXSql.log

  • To create a Dexsql.log, open the Dex.ini file which exists by default in the following location (C:\Program Files\Microsoft Dynamics\GP\Data)
  • Locate the following statements in the Dex.ini file
            SQLLogSQLStmt=FALSE
            SQLLogODBCMessages=FALSE
            SQLLogAllODBCMessages=FALSE
  • If the statements are set to false (which is the default case), change the values to “True”
            SQLLogSQLStmt=TRUE
            SQLLogODBCMessages=TRUE
            SQLLogAllODBCMessages=TRUE

Further illustrative details in creating a Dexsql.log for Microsoft Dynamics GP can be found on the support Article ID: 850996

2- Tracking the security issue

  • Now as the Dexsql.log has been created, you can proceed and open Dynamics GP for the specific user (on the same machine on which the Dex.ini has been modified)
  • To make the process easier, open the screen and clear the log just before clicking the button which caused the security warning (Just to make the log shorter and easier to read)
  • Click on the button which caused the security warning, and then open the Dexsql.log file which exists by default in the following locations (C:\Program Files\Microsoft Dynamics\GP\Data)
  • Look for the command that is calling , zDP_SY10000SS_1 it will tell what’s the exact missing security record

 

DEXSQL

The stored procedure is trying to determine whether the user has an access on this specific security record, here is the select statement being executed by this stored procedure,

      SET nocount ON
      SELECT TOP 1
             USERID ,
             CMPANYID ,
             DICTID ,
             SECURITYID ,
             SECRESTYPE ,
             ALTDICID ,
             Modified ,
             DEX_ROW_ID
      FROM    .SY10000
      WHERE   USERID = @USERID
          AND CMPANYID = @CMPANYID
          AND DICTID = @DICTID
          AND SECURITYID = @SECURITYID
          AND SECRESTYPE = @SECRESTYPE
      ORDER BY USERID ASC ,
               CMPANYID ASC ,
               DICTID ASC ,
               SECURITYID ASC ,
               SECRESTYPE ASC
      SET nocount OFF   

 

If you run this script above after providing the associated parameters, it will retrieve no records, that justifies the security warning since this user has no access for this specific security record.

What is the missing Security Record !

You can simply run the following statement on the [Security Resource Description] table, which will retrieve the specific record that should be granted to the user

SELECT  *
FROM    dbo.SY09400
WHERE   DICTID = 0
        AND SECURITYID = 284
        AND SECRESTYPE = 23

Here is the record to be added,

Security Resource Description

 

Security Task

 

Helping Note !

It is a must to have the security resource description table populated, if the select statement above retrieved no records. That means you should get this table populated first. See the details on How to identify the Security Tasks and Security Roles associated with a specific window or report by David Musgrave

At the end, I should sincerely thank Mr. David Musgrave for the endless source of information provided on developing for Dynamics GP, this Dex.ini switch has been illustrated thoroughly by him back in 2008 on How to Resolve Security errors on Login

 

Best Regards,
Mahmoud M. AlSaadi

5 comments:

  1. You could always use the Security Information window in the Support Debugging Tool as this is even more powerful and easier to use.

    http://aka.ms/SDT

    David

    ReplyDelete
    Replies
    1. Absolutely right David,
      The SDT has always been my number one choice, it is definitely the best thing since sliced bread !

      Mahmoud,

      Delete
  2. Hi Mahmood,
    Thanks for the articel, I always had to go through this manually and would really like to understand it once for all.

    I have tried to run a from SELECT *
    FROM dbo.SY09400 and everything is blank.

    I have even put the DICTID, AND SECURITYID , SECRESTYPE values as per the dexlog and i still get nothing.

    Is there something that I might be missing?
    From the dexlog
    { CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'TEST', 1, 949, 22133, 23 ) }

    So here is my query:


    SELECT *
    FROM dbo.SY09400
    WHERE DICTID = '949'
    AND SECURITYID = '2213'
    AND SECRESTYPE = '23'

    ReplyDelete
  3. Hi Mahmood,

    Thanks a lot, everything is working perfectly after I have run maintenance as per the article below:

    https://community.dynamics.com/gp/f/32/t/150185


    Regards
    Patrick Kalemba

    ReplyDelete
    Replies
    1. Always welcome

      Best Regards,
      Mahmoud M. AlSaadi

      Delete