SQL view with security resource details for Dynamics GP 10

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:

1. Go to Microsoft Dynamics GP > Maintenance > Clear Data
2. Click Display on the toolbar and click Physical
3. Select System under Series
4. Click Security Resource Descriptions under Tables to highlight it and click Insert to add it to the Selected Tables list
5. Click OK, then Yes to the pop up message asking you if you’re sure that you want to clear data from the table
6. 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 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.

Posted in Crystal Reports, Dynamics GP, GP 10.0, GP Reports, GP SQL scripts, SmartList Builder Tagged: Crystal Reports, Dynamics GP, GP 10.0, GP Reports, GP SQL view, GP table information, security, SmartList Builder, SQL code