Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

7 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
  4. Hello Mahmoud,

    Thank you for all you do. I have seen a lot of your work out there and it has been a big help. I had a question regarding the info above. I have been digging into security and am using the above info to pull the task info. I have come across a few entries that are not showing any records in any tables even though I see the security check in the DEXLOG. Example below:

    CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'Test1', 1, 0, 66038, 900 )

    I have checked in SY10000, SY10700 and SY09400. There is no task with that SECURITYID but the system is checking for it. Any thoughts?

    ReplyDelete
  5. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete