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