Developing for Microsoft Dynamics GP by David Musgrave (Australia) and the Microsoft Dynamics GP Developer Support Team (USA)Syndicated From: http://blogs.msdn.com/b/DevelopingForDynamicsGP/
Click on the link to see the full About Page.
From the Microsoft Dynamics GP Application Level Security Series.
Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This model is defined in the following way:
Note: Operations may be assigned to multiple Security Tasks and Security Tasks may be assigned to multiple Security Roles.
In the situation when a system administrator knows which window (or report) they wish to grant access to a user, but does not know what Security Tasks or Security Roles are associated with the window, there is no simple method to obtain this information from within the application. It would be possible to scroll through each Security Task on the Security Task Setup window (Microsoft Dynamics GP >> Tools >> Setup >> System >> Security Tasks and check if the window is selected, but this is time consuming. The Print Operation Access report which can be printed after selecting the window will show which users have access to the window, but not how that access was obtained based on the Security Roles and Security Tasks.
To obtain the data we will use a new Security Resource Descriptions table (Technical Name: syCurrentResources (SY09400) table) which was added to v10.0 to create a SQL Query to obtain the information. This table is initially empty, but can be populated by running the Clear Data File Maintenance process on it. The system will then rebuild the contents based on the current installed dictionaries.
Below are the steps to populate the Security Resource Descriptions table:
Now that the Security Resource Descriptions table has been populated we can use it in a SQL Query from SQL Query Analyzer (SQL Server 2000) or SQL Server Management (SQL Server 2005). The Query below will display the Security Roles and Security Tasks associated with a specific window or report as selected by changing the Display Name on the last line of the query.
SELECT ISNULL(A.SECURITYROLEID,'') AS SECURITYROLEID, ISNULL(M.SECURITYROLENAME,'') AS SECURITYROLENAME, --ISNULL(M.SECURITYROLEDESC,'') AS SECURITYROLEDESC, ISNULL(O.SECURITYTASKID,'') AS SECURITYTASKID, ISNULL(T.SECURITYTASKNAME,'') AS SECURITYTASKNAME, --ISNULL(T.SECURITYTASKDESC,'') AS SECURITYTASKDESC, R.PRODNAME, R.TYPESTR, R.DSPLNAME, R.RESTECHNAME, R.DICTID, R.SECRESTYPE, R.SECURITYIDFROM DYNAMICS.dbo.SY09400 RFULL JOIN DYNAMICS.dbo.SY10700 O ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYIDFULL JOIN DYNAMICS.dbo.SY09000 T ON T.SECURITYTASKID = O.SECURITYTASKIDFULL JOIN DYNAMICS.dbo.SY10600 A ON A.SECURITYTASKID = T.SECURITYTASKIDFULL JOIN DYNAMICS.dbo.SY09100 M ON M.SECURITYROLEID = A.SECURITYROLEIDWHERE R.DSPLNAME = '<Display_Name>'
Note: The <Display_Name> placeholder represents the actual display name. For example, the display name may be "Sales Transaction Entry".Below are the example results based on a default installation for 'Sales Transaction Entry':
If there are no Security Roles assigned to the Security Tasks, they will show as blank. If there are no Security Tasks assigned to the Operation, they will also show as blank.
Security Table Information
Security Operations for a Security Task are stored in table sySecurityAssignTaskOperations (SY10700).Security Tasks are defined in table sySecurityMSTRTask (SY09000).
Security Tasks for a Security Role are stored in table sySecurityAssignTaskRole (SY10600).Security Roles are defined in table sySecurityMSTRRole (SY09100).
Security Roles for a User and Company combination are stored in table sySecurityAssignUserRole (SY10500).
Ref: Portions from KB 951229
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13