Security roles and tasks in Dynamics GP can be very confusing, especially if you didn’t design it from the ground up yourself. I, for one, took over as the Great Pains administrator after the system had already been in place for a couple of years. The security model worked just fine at the time but over the years the company has grown and new roles had been put in place. This led to quite a bit of piggy-backing of security roles, ad hoc tweaks to individual users and new roles being created while others were being abandoned.
When the time came to start cleaning it up and getting a better grasp on the security within GP the first step I needed to do was to identify all security roles that were currently not in use. This way I could go ahead and remove them without worrying about disrupting anyone’s permissions.
The following script will do just that.
SELECT sr.securityroleid AS SecurityRoleID,
sr.securityrolename AS SecurityRoleName,
sr.crusrid AS CreatedBy,
sr.creatddt AS CreatedDate
FROM SY09100 sr
LEFT JOIN SY10500 sy ON sr.securityroleid = sy.securityroleid
WHERE sy.securityroleid IS NULL
This script pulls from the Security Roles Master System table as well as the Security Assignment User Role System table in order to determine which roles are not currently assigned to any users. Any SecurityRoleID that is returned by this script can safely be deleted in GP without causing any issues for your users.
I would recommend handing any role deletions from the GP front-end as deleting these directly from the SY01900 table will not fully remove them from the system and could cause some issues down the road.