Dynamics GP offers excellent tools for managing user security within the application. Assuming as security model is created during implementation, you should have a standard for how permissions are assigned and modified within your Great Plains environment. Unfortunately this is now always the case. During a recent clean-up project I discovered that I needed an easier way to review certain individual user’s permissions to assist in determining if they had access to certain modules.
As I mentioned previously, GP does have a great security GUI for this but it does require you review multiple security roles as many of our users are assigned to multiple roles. Within each role there can be countless security tasks configured as well.
The following script greatly simplifies this process when you want to review a single user’s security profile.
SELECT s.USERID AS UserID,
cm.CMPNYNAM AS CompanyName,
COALESCE(rt.SECURITYTASKID,'') AS SecurityTaskID,
COALESCE(tm.SECURITYTASKNAME,'') AS SecurityTaskName,
COALESCE(tm.SECURITYTASKDESC,'') AS SecurityTaskDescription
FROM SY10500 S
LEFT OUTER JOIN SY01500 cm ON s.CMPANYID = cm.CMPANYID
LEFT OUTER JOIN SY10600 rt ON s.SECURITYROLEID = rt.SECURITYROLEID
LEFT OUTER JOIN SY09000 tm ON rt.SECURITYTASKID = tm.SECURITYTASKID
WHERE USERID = 'Jack' --insert user ID here
ORDER BY rt.SECURITYROLEID
Before executing the script be user to replace the
USERID in the
WHERE clause. Below is an example of this script being run using my personal
USERID in my GP lab environment.
Using the data provided by this script will allow you to better trailer your individual roles and tasks within Great Plains. I also have another post about identifying unassigned security roles within GP which can be used to help you determine if you have orphaned roles that can be removed completely.