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

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.

 

 

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

 

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,

 

 

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

Audit Requirements for Dynamics GP

The need to keep a log for every single modification on the system usually never comes without considerable cost either from a technical or business perspective. Although, the need for such requirement remains crucial. In this post, I am shedding a light specifically on the need to keep track of every single modification of a record in a simple and yet cost effective methodology.

Technical Perspective

There are several methods to accomplish such requirement; very great and well know third party products can just deliver the need, techniques such as triggers, timestamp columns, join queries … etc could be a time-consuming which sometimes result in an undesirable performance. Meanwhile, there is a great feature out there in SQL management which can give you all these requirements with a low-cost, it is called the “Change Data Capture - CDC”

SQL Server | Change Data Capture – CDC

Change data capture provides information about the DML (Data Manipulation Language) changes on a table or database, it is very useful to know what are the inserted or deleted records, what are the values of the updated records (before and after the update).

The important of CDC will be explained through a simple example of updating a vendor card, which got few fields updated from a specific value to another value. CDC will provide detailed information of the data before and after update as shown below

Configuring Change Data Catalog for Dynamics GP

Once configured, CDC builds new system tables, stored procedures, SQL jobs and functions. Initially, you can run the scripts below to check whether CDC is enabled either on the database level or table level.

To check whether the CDC is enabled on the database level

-- The script below checks whether CDC is enabled for each database
USE master
SELECT  is_cdc_enabled ,
database_id,
name,
state_desc,
create_date,
user_access_desc,
is_read_only,
snapshot_isolation_state_desc,
recovery_model_desc
FROM    sys.databases     

To check whether the CDC is enabled on the table level

-- The script below checks whether CDC is enabled for each table
USE TWO
SELECT object_id,type_desc,name,is_tracked_by_cdc
FROM sys.tables

 

 

 

 

In the screen shot above, it is obvious that CDC is enabled on the TWO db, and specifically on the IV00101 table which is the Item Master. In order to enable CDC on the DB and Table, run the following scripts:

To enable the CDC on the database level:

-- The script below enables CDC on the database level
USE TWO
EXEC sys.sp_cdc_enable_db

To enable the CDC on the table level:

-- The script below enables CDC on the table level

USE TWO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'IV00101',
@role_name     = NULL

Once CDC is enabled, the following objects are created under TWO db > Tables > System Tables

Important Tables:

cdc.change_tables | Storing the tables being tracked
cdc.captured_columns | Storing the column being tracked per table

 

Real Case Scenario

After enabling the CDC on the TWO db, and the item master table specifically IV00101, I am going to apply the following scenario in order to check the result:

1. Adding a new item card
2. Changing few fields of the item such as; item class, UOM schedule, item description …etc
3. Delete the item

Here is the tracking log as retrieved from [cdc].[dbo_IV00101_CT]  on the following path  (TWO db > Tables > System Tables), the table shows the different changes applied on the item card since it was inserted, updated then deleted.

 

Here is the script which is used to retrieve tracking information above, it should be different according to the table for which you enable CDC.

SELECT CASE __$OPERATION
         WHEN 1 THEN 'Deleted'
         WHEN 2 THEN 'Inserted'
         WHEN 3 THEN 'Before being Updated'
         WHEN 4 THEN 'After beign Updated'
         ELSE ''
       END AS Record_Status,
       *
       FROM [cdc].[dbo_IV00101_CT]

 

In brief, change data catalog feature is a simple and cost-effective method that can be applied to track highly sensitive data such as setup in general. In proceeding posts, I will include a real case example of tracking opening and closing fiscal periods on the financial module and provide alert accordingly.

Best Regards,
Mahmoud M. AlSaadi

User Security Role Enhancement–Intersect

Monitoring user security privileges in Dynamics GP might be a bit of headache sometimes. Therefore, several GP experts have provided SQL views to collect the security roles and tasks for a certain user in order to simplify this process. Although, several inquiries might still require enormous time especially when it comes to give a certain GP user bits and pieces of other user privileges. 

In this post, I am proposing a new methodology in enhancing this process by taking the SQL view a bit forward to further simplify such requests.

Practical Example:

Suppose you may have a problem with a certain user (User A) who’s not being able to access a certain window (transaction, report, inquiry or posting privileges). On the other hand, (User B) is not encountering the problem.

The methodology I am supposing is to intersect the security privileges of both users (A and B) in order to eliminate the security tasks range in which the required access resides. See the chart below for graphical illustration

In this essence, the idea embedded within the chart above must be broken down into pieces in order to understand the proposed enhancement:

• Check the security privileges (Roles and Tasks) for both users (A and B). The script to check the user security is provided at the end of the post
•  Build the logic between the two scripts using one of the following (in or not in)

 

The script below retrieves the security role and associated tasks for a selected GP User. It represents the basic block of the method above.

Tables Included:

• SY10500 | Security Assignment User Role
• SY01500 | Company Master
• SY10600 | Security Assignment Role Task
• SY09000 | Security Tasks MASTER

 

SELECT  RTRIM(SR.CMPNYNAM) + '__' + RTRIM(SR.USERID) + '__'
         + SR.SECURITYROLEID AS 'Company User Role' ,
SR.CMPNYNAM AS 'Company Name' ,
USERID AS 'GP User ID' ,
ST.SECURITYROLEID AS 'Security Role ID' ,
ST.SECURITYTASKID AS 'Security Task ID' ,
ST.SECURITYTASKNAME AS 'Security Task Name' ,
ST.SECURITYTASKDESC AS 'Security Task Description'
FROM    ( SELECT    [CMPNYNAM] ,
                    [USERID] ,
                    [SECURITYROLEID]
                    FROM      [SY10500]  AS A
                    INNER JOIN [SY01500] AS B ON A.[CMPANYID] = B.[CMPANYID]

         ) AS SR
        LEFT OUTER JOIN ( SELECT    C.[SECURITYROLEID] ,
                                    C.[SECURITYTASKID] ,
                                    D.[SECURITYTASKNAME] ,
                                    D.[SECURITYTASKDESC]
                          FROM      [SY10600] AS C
                          INNER JOIN [SY09000] AS D ON C.[SECURITYTASKID] = D.[SECURITYTASKID]
                         ) AS ST 
                           ON ST.SECURITYROLEID = SR.SECURITYROLEID

Best Regards,
Mahmoud M. AlSaadi