As a corollary to my view showing the Dynamics GP 10.0 security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim Foster for pointing me to a newsgroup post by David Musgrave detailing how to populate the SY09400 table in SQL which holds the system resource details for GP 10.0. I have listed the instructions below:
Go to Microsoft Dynamics GP > Maintenance > Clear Data
Click Display on the toolbar and click Physical
Select System under Series
Click Security Resource Descriptions under Tables to highlight it and click Insert to add it to the Selected Tables list
Click OK, then Yes to the pop up message asking you if you’re sure that you want to clear data from the table
Send the report to the screen, it should report back with ‘No errors found’
Now you can use the view below. The view will still work without this table being populated, but all the resource details will be blank. Create this view against the DYNAMICS database. (Added on Sep 19, 2011: Please note that even though the title says GP 10, this will also work for GP 2010.)
CREATE VIEW view_Security_Details AS -- view_Security_Details -- Created Mar 20 2009 by Victoria Yudin - Flexible Solutions, Inc. -- For updates see http://victoriayudin.com/gp-reports/ -- Shows all security roles, tasks and detailed resource descriptions by user by company 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 --the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions GO GRANT SELECT ON view_Security_Details TO DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
For other SQL code, please check out the GP SQL Scripts section my GP Reports page.