ERP Software Logo

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

Stoneridge Software

How to Migrate from Converted Security in Microsoft Dynamics GP


    Email | Print

    Recently an auditor recommended that one of our Microsoft Dynamics GP customers review their security permissions and make it easier to manage within GP. However, altering those permissions to make them simpler isn’t an easy step.

    Dynamics GP was built on a security model which required companies to maintain the list of windows, reports and other resources that users would not be able to access. When a user record exists in the security model for a window form or process, Dynamics GP would prevent them from using said resource. Then along came the Public Company Accounting Reform and Investor Protection Act, otherwise known as Sarbanes-Oxley or SOX. To be compliant with federal regulations, Dynamics GP built a new security model which was flipped over to the opposite design, one where the system users who maintain the security model were required to add a record to the security model for each resource.

    At the time of this release, any new Dynamics GP customers were given a set of default roles and tasks for use within the system, and user activity was controlled to act only within the confines of those defined set of records. At the same time, a conversion process was included to help Dynamics GP’s set of existing customers to get up and running their business. As a result of this conversion process, a set of roles and tasks with names like CNV_LESSONUSER1 appeared inside the data-model alongside the predefined ones with names like PURCHASING AGENT and DEFAULTUSER. This blog post is help people migrate from CNV_ to predefined security model.

    The CNV_ security model is overwhelmingly difficult to manage. There are thousands of extra records created by the conversion process. When new people are hired into Dynamics GP user roles, they get assigned the conversion set that was converted by the person for who they were hired to replace. It’s a huge mess! In order to clean up the records, they need to be migrated over, and then extra records need to be deleted.

    1. Update Security Resource Definitions
    • Navigate to Microsoft Dynamics GP, Clear Data

    • Select System Series and Display Physical, Security Resource Desc., Insert then OK

    • Use this query to see the data

    Select 'SY09400' as TableName, 'syCurrentResources' as TechnicalName,* from SY09400 order by DEX_ROW_ID desc
     

    1. Turn on Activity Tracking for Users
    • Navigate to Microsoft Dynamics GP, Setup, System, Activity Tracking

    • Turn it on for company and user.

    • Use this query to see the data

    select 'SY05000' as TableName, 'SY_Security_Log' as TechnicalName,* from SY05000 order by DEX_ROW_ID desc

    1. Track User Activity over a good time period when the system has been used by the user.
      • Use this query to create a view of usage with proper name extraction from message.
        • create view ATWindowsReports as select REPLACE(REPLACE(REPLACE(SECDESC,'The ',''),' window was successfully opened.',''),' was printed.','') as 'ATName',* from SY05000
      • Use this query to create a view of currently tracked window and form activity per user.
        • create view ATWindowsReportsAnalyzer as select
          a.ATName,a.INDEX1,a.PRODNAME,a.CMPNYNAM,a.USERID,a.INQYTYPE,a.DATE1,a.TIME1,a.SECDESC,b.DICTID,b.SECRESTYPE,b.TYPESTR,b.SECURITYID,b.RESTECHNAME,b.DSPLNAME,b.SERIES,b.Series_Name,b.AvailLmtdUsrs from ATWindowsReports a JOIN SY09400 b ON a.ATName = b.DSPLNAME
    2. Compare the users usage with the predefined roles inside Dynamics GP
      • Use this query to see all the tasks in the system
        • select 'SY09000' as TableName, 'sySecurityMSTRTask' as TechnicalName,* from SY09000 order by DEX_ROW_ID desc
      • Use this query to see all the operations assigned to these tasks
        • select 'SY10700' as TableName, 'sySecurityAssignTaskOperations' as TechnicalName,* from SY10700 order by DEX_ROW_ID desc
      • Use this query to see all the roles in the system
        • select 'SY09100' as TableName, 'sySecurityMSTRRole' as TechnicalName,* from SY09100 order by DEX_ROW_ID desc
      • Use this query to see all the tasks assigned to each role in the system
        • select 'SY10600' as TableName, 'sySecurityAssignTaskRole' as TechnicalName,* from SY10600 order by DEX_ROW_ID desc
      • Use this query to see all the non-conversion roles that need to be assigned to each user
        • select distinct USERID,SECURITYROLEID,TYPESTR,DSPLNAME from ATWindowsReportsAnalyzer a JOIN SY10700 b ON a.DICTID = b.DICTID AND a.SECRESTYPE = b.SECRESTYPE AND a.SECURITYID = b.SECURITYID JOIN SY09000 c ON b.SECURITYTASKID = c.SECURITYTASKID JOIN SY10600 d ON c.SECURITYTASKID = d.SECURITYTASKID where SECURITYROLEID not like 'CNV_%'
    3. Sample screenshot of the data associated with user ROSIE
    4.  

       

      If you need help restructuring your security settings, reach out to the professionals at DFC Consultants. 

      By Robert Wagner, Principal Developer
      DFC Consultants

    Ask This Expert a Question / Leave a Comment

     

     
     
    Show Buttons
    Hide Buttons