Since security has become more complicated in Dynamics GP, there are times when it becomes difficult to figure out what tasks a user needs assigned in order to access a certain window. In the beginning of this blog post, we will show you the detailed information and how we previously did this. In the later half of this post, we provide a new script that you can run that will help automate this process for you.
Usually the main security issue is that a user tries to access a certain window and get an error saying they do not have permission to open that window. Initially, you could determine the requirements by creating a DEXSQL log of the permission error. Start this log just at the moment before you click the link or open the window, then open the dex sql log file. When you open the file, you will see a line similar to this:
CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( ‘sa’, 1, 0, 950, 23 ) }
‘sa’ is the ID of the user whose security is being checked.
1 is the Company ID where the security is being checked.
0 = (DICTID) is the Product ID of the window or the report that is opened.
950 = (SECURITYID) is the Unique ID for the window or the report that is opened.
23 = (SECRESTYPE) is the Resource type. A value of 2 indicates a window, and a value of 23 indicates a report.
Based on these fields you can do a select statement against the SY09400 table (Security Resource Descriptions). However, this table does not come pre-populated in Dynamics GP and you have to populate it first with the following steps:
1) Log into GP 10.0 as ‘sa’. No other users can be logged in at this time
2) Go to Microsoft Dynamics GP > Maintenance > Clear Data
3) Under the Display menu select ‘Physical’
4) Select the ‘System’ series
5) Find the ‘Security Resource Descriptions’ table, and insert it to the right
6) Click OK and allow the operation to process
There are two different scripts that we have found that make narrowing down what window is assigned to what tasks and what roles and tasks are assigned to a user. They are included below:
This one will tell you all the tasks with access to a Window. Replace the “<Display Name>” with the name of the window in GP:
declare @window varchar(50)
set @window='<Display Name>’
select a.SECURITYTASKID as ‘Security Task’, c.SECURITYROLEID as ‘Role that the task is assigned to’, b.DSPLNAME ‘Window name’ from DYNAMICS..SY10700 a
left outer join DYNAMICS..SY09400 b on a.securityid=b.securityid
left outer join DYNAMICS..SY10600 c on a.SECURITYTASKID=c.SECURITYTASKID
where a.SECRESTYPE=2 and b.DSPLNAME = @window
order by a.SECURITYTASKID
This one will tell you all the tasks and roles a particular user has. Replace the “xx” with the user ID in GP:
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
where USERID = ‘XX’ order by CompanyID
So if you just want to figure out what tasks give access to a certain window, you can populate the SY09400 table and run the first script above. If a user is getting an error when trying to access a certain window you can also run the first script for that window and then make sure the user has each of the security tasks the script shows.
How to create a Dexsql log file to troubelshoot error messages in Microsoft Dynamics GP: https://support.microsoft.com/en-us/kb/850996
Dan Moore, Boyer & Associates, Dynamics GP, NAV and SL Implementation & Support, Minneapolis, MN.
