Monday, April 18, 2011

Resetting Security Roles and Task with the Support Debugging Tool

This is my first after Convergence post and I have to say, I came away with enough questions from attendees to fill the blog for the remainder of the year. One such questions came from someone attending the interactive discussion session on security, IDGP04-R2 Tips and Tricks for Maintaining Security in Microsoft Dynamics GP - see Microsoft Dynamics Convergence Atlanta 2011: Day 4 Morning Closing Session (Cont.) for more on what transpired that day.

The individual in question wanted to know if there is a way or method to "reset" all Microsoft Dynamics GP security roles and tasks to the out-of-the-box defaults once these had been changed. After thinking about it for a bit, there is effectively a way using the Support Debugging Tool.

Step 1 - Export the security tables from a brand new installation of Microsoft Dynamics GP
In a brand new installation of Microsoft Dynamics GP, install and use the Support Debugging Tool's XML Table Export feature to create a SECURITY profile ID, including all the security tables as shown below.

XML Table Export - SECURITY profile ID
 Step 2 - Optional: Clear the destination system's security tables

You can then create a script using the Support Debugging Tool's SQL Execute that will clear all the security tables in your destination system, as shown below.

SQL Execute - Clears security tables
By saving the script ID we can reuse the code as many times as needed or for future resets. Note also, that I have used the Dexterity table notation in the DELETE statement as I already had the names in the XML Table Export window.

Step 3 - Import the records exported in step 1 into the destination system

Finally, we can use Support Debugging Tool's XML Table Import feature to reset security and reload the defaults from the XML file exported in step 1. Note that I have marked the Overwrite Table Contents option, which would render step 2 not necessary. However, having step 2 reduces your chances of forgetting to select this checkmark.

XML Table Import
I have attached the SECURITY profile ID and the RESET_SECURITY script ID at the bottom of this post. I am also including the exported security tables XML files with the default security tasks, roles and assignments. Remember that you can choose a reduce set of data to export or remove tables that you may not want to import. It all depends what level of granularity you are trying to achieve.

Downloads - contains SECURITY profile ID, RESET_SECURITY script ID, and default security roles, tasks and operations for Microsoft Dynamics GP 2010.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


Willy said...


Thanks for the post. This is exactly wahat I needed when I ran across someone who needed a security reboot. I did want to point out one thing. You included importing the SySecurityView.

Which is a view and the import fails.

I believe you need to include both SY10600 and SY10700 which are part of the view above.

Thanks again

Mariano Gomez said...

Thanks for the tip. Clearly need to.

Bill Breitenbach said...


Do you have this script for GP 2015? I have found that security tasks were missing when I upgraded from GP 2010 based on and others like access to the GL Transaction Paste Validation Report.