Share via

How to transfer Security Tasks and Roles between Systems

  • Article

David Meego

From the Microsoft Dynamics GP Application Level Security Series.

With the change from the optimistic user and class based security model in Microsoft Dynamics GP v9.0 (and v8.0) to the pessimistic role and task base security model in Microsoft Dynamics GP v10.0, a question that is often asked is how can security settings be transferred between a test and live system.

With version 8.0 and 9.0, you could use the Export and Import facility built into Advanced Security to transfer the settings for a user/company or class as an XML file. As Advanced Security is no longer relevant with the role and task based model, that option is not available for version 10.0.

The alternative for version 10.0 is to copy the data from the appropriate tables from source system to the target system. You can copy the data using a variety of methods, but the simplest would be to use the XML Table Export and XML Table Import features of the Support Debugging Tool for Microsoft Dynamics GP. As we will be using Advanced Mode features, we will need to log in as a user with administrator permissions.

Below are the steps, please skip steps already completed previously: 

  1. Click here for the links to download the tool from PartnerSource.
     
  2. Extract the archive files to your application folder.
     
  3. Launch Microsoft Dynamics GP and select Yes to include new code.
     
  4. Log into Microsoft Dynamics GP as 'sa' or a user with administrator permissions.
     
  5. From the menus click Microsoft Dynamics GP, click Tools, click Support Debugging Tool.
     
  6. From the Support Debugging Tool window, click Options, click Dex.ini Settings.
     
  7. On the Debug Tab, in the Support Debugging Tool Options section, select the Enable Debugger Advanced Mode Features checkbox.  If a System Password is in use, you will need to enter it.
     
  8. Click OK to close the Dex.ini Settings window.
     
  9. From the Support Debugging Tool window, click Options, click XML Table Export. If a System Password is in use, you will need to enter it. 
     
  10. Add the tables to Export from the Microsoft Dynamics GP product in the System Series.  The table below shows the tables to use.  If you wish to transfer the settings without the actual user assignments, leave off the final two tables.
Table Name Physical Name Description
sySecurityMSTRRole SY09100 Master table for Security Roles
sySecurityAssignTaskRole SY10600 Assignment table for Security Roles to Tasks
sySecurityMSTRTask SY09000 Master table for Security Tasks
sySecurityAssignTaskOperations SY10700 Assignment table for Security Tasks to Operations
sySecurityMSTRModAlt SY09200 Master table for Alternate/Modified Groups
sySecurityAssignModAltOperations SY10800 Assignment table for Alternate/Modified Operations
     
sySecurityAssignUserRole SY10500 Assignment table for Users to Security Roles
sySecurityAssignUserModAlt SY10550 Assignment table for Users to Alternate/Modified Groups

The window should look similar to the following screenshot: XML Table Export

Table Name Physical Name Description
sySecurityMSTRRole SY09100 Master table for Security Roles
sySecurityAssignTaskRole SY10600 Assignment table for Security Roles to Tasks
sySecurityMSTRTask SY09000 Master table for Security Tasks
sySecurityAssignTaskOperations SY10700 Assignment table for Security Tasks to Operations
sySecurityMSTRModAlt SY09200 Master table for Alternate/Modified Groups
sySecurityAssignModAltOperations SY10800 Assignment table for Alternate/Modified Operations
     
sySecurityAssignUserRole SY10500 Assignment table for Users to Security Roles
sySecurityAssignUserModAlt SY10550 Assignment table for Users to Alternate/Modified Groups

The window should look similar to the following screenshot:

XML Table Export
  1. Change the Export Path if desired. 
     
  2. Click Export to export the data into the specified XML file.

On the target system, install the Support Debugging Tool and activate Advanced Mode as before.

  1. From the Support Debugging Tool window, click Options, click XML Table Import. If a System Password is in use, you will need to enter it. 
     
  2. Select the Import Path to point to the previously exported XML file.
     
  3. Select the Overwrite Table Contents checkbox to remove the old settings before importing the new settings.
     
  4. Select the tables you wish to be imported.
     
  5. Click Import.
     
  6. When completed, close the window.

Now the data from the source system will have been copied to the target system. If you did not copy the User Assignments, you can now use the Microsoft Dynamics GP v10.0 user interface to select the Security Roles and Alternate/Modified ID for each user and company combination.

Note: A quick note about SmartLists added using SmartList Builder.  If the systems do not have the exact same SmartList Builder configuration (by copying data files), it is possible for the SmartList Security IDs to be different between systems.  In this case copying security between the systems could give incorrect results.  The idea is that the test system is the same as the live system, same dictionaries installed, same data.

To speed up the selection of the tables for export, a settings file has has been attached to the bottom of this post which can be loaded using the Configuration Export/Import window.

For more information on the role and task based security model, see the Knowledge Base (KB) article below:

Frequently asked questions about role-based security in Microsoft Dynamics GP 10.0 (KB 951229)** ** Secure Link 

Just a little addition, the SnapShot tool can also be used to transfer data between systems. For more information have a look at the SnapShot for Microsoft Dynamics GP post.

David

10-Nov-2008: Add link to SnapShot as another method.

07-Apr-2010: Added warning about custom SmartLists added by SmartList Builder.

Debugger Settings.dbg.zip

Comments

  • Anonymous
    November 09, 2008
    For the next couple of weeks, I have decided to focus on Microsoft Dynamics GP application level security and methods for resolving security related issues.

  • Anonymous
    November 10, 2008
    David Musgrave over at Developing for Dynamics GP is tackling Dynamics GP 10 Security in a new series

  • Anonymous
    November 10, 2008
    From the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/11/microsoft-dynamics-gp-10-security.html

  • Anonymous
    November 10, 2008
    Dave, Is it necesssary to log in with a user that has admin privileges in GP or a admin rights in SQL for the specified tables? Thanks Andrew

  • Anonymous
    November 11, 2008
    Hi Andrew All the Advanced mode functions of the Support Debugging Tool (including XML Table Export and XML Table Import) required the user to have database owner or administrator rights at the SQL level.  They would also need security access to the actual forms and if a system password is in use, they would need that to. Because the Advanced Mode features can allow access to the data directly, they are all protected from use by unauthorised users. David

  • Anonymous
    November 23, 2008
    One of the great things about blogging is the ability to inform and educate partners and customers on

  • Anonymous
    November 23, 2008
    From the Microsoft Dynamics GP Application Level Security Series . This is the final article in this

  • Anonymous
    July 31, 2009
    Wil this tool (or possibly another tool available) allow you to transfer security from 1 company to many other companies all in 1 step?

  • Anonymous
    July 31, 2009
    Denise, No this tool won't do that.  It is merely (in 2 steps) copy all the records from one database into another database as-is.  It doesn't make any changes. What you would need to do is copy the security records for one user/company and then insert the modified records (with the new company id) back to the same table.

  • Anonymous
    March 02, 2010
    I was really excited to find this posting. Unfortunately for me the import side of the process failed. I ended up using DTS to accomplish this task.

  • Anonymous
    March 02, 2010
    Hi Chris Can you explain what failed with the import side of the process? Did you select to Overwrite Table Contents? Thanks David

  • Anonymous
    September 05, 2010
    Posting from Jivtesh Singh at About Dynamics, Development and Life www.jivtesh.com/.../dynamics-gp-sdt-essential-series-2.html

  • Anonymous
    December 07, 2010
    David - This is very helpful. I have additional question - I performed TEST upgrade from GP9 to GP2010 - In TEST - I setup configured Security from scratch. I plan backing up my DYNAMICS from my TEST, then when I  perform the real upgrade - restoring DYNAMICS database from the backup. Would this eliminate the need to export and re-import security? Thanks for any guidance.

  • Anonymous
    December 07, 2010
    Hi Bron This would eliminate the need to export and re-import security.  However, it will also wipe out any changes made to the DYNAMICS database in the time between the test upgrade and the live upgrade. You would be creating the situation for a number of issues including cross linked notes and problems with multi-currency. blogs.msdn.com/.../understanding-notes-and-the-note-index-field.aspx I would recommend against this approach. David

  • Anonymous
    April 22, 2011
    Posting from Terry Blaser at Dynamics GP Support and Services Blog blogs.msdn.com/.../how-to-move-your-security-settings-from-test-into-production.aspx

  • Anonymous
    July 26, 2011
    Posting by Rubal on Dynamics GP Help dynamicsgphelp.com/.../sql-where-clause-to-xml-export-window-support-debugging-tool-build-15

  • Anonymous
    February 11, 2014
    Hi David, When you say copying security between systems could give incorrect results for Smartlist Builder reports, can you please clarify what kind of issues? I want to copy the role and task settings from one of my server to another with a different SmartBuilder set of reports, but I am worried about the impact on the SmartBuilder reports in the target client. Thanks!

  • Anonymous
    February 11, 2014
    Hi Chico Smartlist builder generates an Security ID to be used when the Smartlist is created. This ID is used in the security task setup to reference the Smartlists. For a system with the same Smartlists added, it is likely that the Security IDs for each Smartlist are different, unless they were added in the same order or copied at the table level. For a system with different Smartlists, they will be different. I suggest not setting security for any Smartlist builder favourites until on the final system. David