A common request we receive is: "We want to tie down security of our users but have no clue as to who has access to what". To be frank, the reporting for this in GP is not exactly easy to come by. While there are a number of ISVs available that will provide you with tools and analytics for this information, we have a quick fan-favorite solution via SQL to gather this data.
This information can also be found on the Microsoft Developer Blog here.
First thing's first, in order to get the following script to populate data, the Clear Data Procedure on the Security Resource Descriptions table within GP. This can be done from Microsoft Dynamics GP>>Maintenance>>Clear Data
Select 'Display' in the toolbar change to 'Physical', Series = 'System' & Table = Security Resource Descriptions.
After inserting the table and running the clear data procedure, the SQL query will now populate the users>>roles>>tasks>>operations which can be used as a starting point to identify and cleanup security/internal control concerns.
SELECT DISTINCT S.USERID UserID, S.CMPANYID CompanyID, C.CMPNYNAM CompanyName, S.SecurityRoleID, coalesce(T.SECURITYTASKID,'') SecurityTaskID, coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName, coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription, coalesce(R.DICTID,'') DictionaryID, coalesce(R.PRODNAME,'') ProductName, coalesce(R.TYPESTR,'') ResourceType, coalesce(R.DSPLNAME,'') ResourceDisplayName, coalesce(R.RESTECHNAME,'') ResourceTechnicalName, coalesce(R.Series_Name,'') ResourceSeries FROM SY10500 S -- security assignment user role LEFT OUTER JOIN SY01500 C -- company master ON S.CMPANYID = C.CMPANYID LEFT OUTER JOIN SY10600 T -- tasks in roles ON S.SECURITYROLEID = T.SECURITYROLEID LEFT OUTER JOIN SY09000 TM -- tasks master ON T.SECURITYTASKID = TM.SECURITYTASKID LEFT OUTER JOIN SY10700 O -- operations in tasks ON T.SECURITYTASKID = O.SECURITYTASKID LEFT OUTER JOIN SY09400 R -- resource descriptions ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID
Without the Clear Data procedure ahead of time, the resource descriptions table would be empty and provide no detail on the security operations.
Security is never fun and sometimes hard to grasp your arms around. We hope this report helps. Please leave us any questions or feedback below & be sure to our out website @ ALaCarteGP.com.
Until Next Time!