Today’s article comes about because I was asked if GP Power Tools could easily assign a Security Role to all users in all companies in a Microsoft Dynamics GP system.
GP Power Tools does have a copy feature in the Enhanced Security window which can copy all security settings from a selected user/company to all users in the same company or all companies for the same user, but it does not have the option to push a specific Security Role ID across the entire system.
No problem. We can just use the Developer Tools module to create this functionality in an hour or two.
Simple is not always best
My initial thoughts for what the approach should be was based on creating a SQL insert statement to add the Security Role ID into the sySecurityAssignUserRole (SY10500) Security Assignment User Role table for all user/company combinations in the SY_User_Company_Access_REL (SY60100) User-Company Access table.
But then I thought it would be worth making the code smarter and provide a user interface to make it really simple to use. The simple query approach would have had the following issues:
- Does not take into account that user/company combinations that are POWERUSERS or GPPT SUPERUSERS don’t need the Security Role ID added.
- It would error out if a duplicate key error if the Security Role ID was already assigned to a user/company combination.
- It did not confirm that the Security Role ID actually exists in the sySecurityMSTRRole (SY09100) Security Roles Master table.
- There was no user interface and no flexibility to select users if you did not want to apply to all users and companies.
Note: The SUPERUSER Security Role is created and maintained by GP Power Tools and provides access to all operations in the Microsoft Dynamics GP. Unlike POWERUSER which bypasses security entirely, it can be audited. It is based on a concept proposed by Mark Polino.
Adding some complexity makes it better
So this is what I came up with…
Starting with the User Security Setup window I added a couple of menu options using triggers. Giving the choice to add the Security Role to all users and companies combinations without a preview, or to display the users and companies that need the Security Role added and then take further actions.
Once you select either option, a parameter list will ask which Security Role ID you want to add. This uses a custom lookup to select the Security Role ID.
If you selected the no preview option, the Security Role ID selected will be applied to all users who currently don’t have that Security Role ID and don’t have the POWERUSER or SUPERUSER Security Role IDs assigned to them. A dialog will be displayed once completed.
If you selected the preview option, the list of eligible users is displayed in a SQL Results window.
This window has SQL Gotos enabled which then allow you to select the next action.
You now have the following options:
- Double click on a row and it will execute the first Goto to open the selected user and company on the User Security Setup window.
- Click, shift click and control click to select users and companies and then use the second Goto to apply the Security Role ID only to the selected user/company combinations.
- Select the third Goto option which will apply the Security Role ID to all displayed user/company combinations.
- Or close the window to take no further action.
How the Customization was created
The Project Setup window contains all the components that make the feature work. It is the starting point for any customizations created with GP Power Tools – Developer Tools module.
The customization contains the following triggers and scripts:
Trigger SECURITY ROLE I
Trigger to add an additional menu option for Inserting the Security Role ID without any preview. After using the SECURITY ROLE parameter list to ask the user for the Security Role ID, it will execute the SECURITY ROLE I SQL Execute Script to perform the insertion of the records.
Trigger SECURITY ROLE P
Trigger to add an additional menu option for Inserting the Security Role ID with a preview. After using the SECURITY ROLE parameter list to ask the user for the Security Role ID, it will execute the SECURITY ROLE P SQL Execute Script and display the results in a SQL Results window.
Runtime Execute Script SECURITY ROLE A
This script is configured as a SQL Goto Handler and will loop through the selected user/company combinations from the SQL Results window and use Dexterity table commands to add the Security Role ID records.
Runtime Execute Script SECURITY ROLE I
This script is configured as a SQL Goto Handler which will load and execute the SECURITY ROLE I SQL Execute Script to perform the insertion of the records to all users and companies.
Runtime Execute Script SECURITY ROLE S
This script is configured as a SQL Goto Handler which open the User Security Setup window to the currently selected user.
SQL Execute Script SECURITY ROLE D
This script is SQL query used to display the list of Security Role IDs in a Drop Down List and is used by the SECURITY ROLE parameter list. It is currently hidden, but was just to demonstrate an alternative to a lookup.
SQL Execute Script SECURITY ROLE I
This script is SQL query which contains the Insert statement to assign the Security Role ID to all user/company combinations unless they are already assigned or are assigned to POWERUSER or SUPERUSER.
SQL Execute Script SECURITY ROLE L
This script is SQL query used to get the Security Role data to display in a SQL Lookup and is used by the SECURITY ROLE parameter list. It is also used to validate the data once selected.
SQL Execute Script SECURITY ROLE P
This script is SQL query which contains the select statement to Preview the user/company combinations that currently are not assigned to the Security Role ID and are not assigned to POWERUSER or SUPERUSER.
Parameter List SECURITY ROLE
This is how the user is asked which Security Role ID they want to assign. It has a dropdown list alternative which can be moved to the first position and the hidden checkboxes changed to show it and hide the lookup option.
Message SECURITY ROLE
A Message ID which is used to notify the user that the no preview option was selected and has executed. Using a message allows the same text to be used in multiple locations and can handle multiple languages if needed.
Downloading and Installing
Download the example code, import using the Project Setup window (now possible for first time imports with Build 28.8 onwards), or use Configuration Export/Import window (for older builds):
The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.
For more information see:
- How to transfer Security Tasks and Roles between Systems
- GP Power Tools Portal: http://winthropdc.com/GPPT
- GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
- GP Power Tools Videos: http://winthropdc.com/GPPT/Videos
This article was originally posted on http://www.winthropdc.com/blog.