Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on security requires a completely new set of tables. Below is a view to show the security roles and tasks assigned to each user in each GP company.  And for a lot more information about GP security, take a look at David Mugrave’s Microsoft Dynamics GP Application Level Security Series.

~~~~~

CREATE VIEW view_Security_Roles_and_Tasks
AS

/**
view_Security_Roles_and_Tasks
Created Mar 12 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all security roles and tasks by user by company
**/

SELECT	S.USERID UserID, S.CMPANYID CompanyID,
	C.CMPNYNAM CompanyName, S.SecurityRoleID,
	coalesce(T.SECURITYTASKID,'') SecurityTaskID,
	coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,
	coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription

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 

/** 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_Roles_and_Tasks 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.

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, security, SmartList Builder, SQL code