The Case Of The Pesky Window and Security

Maybe I am kidding myself when I think that everyone has been there. You define security in GP 10.0 (or GP 2010 for that matter), and it seems so simple. For example, the client wants to limit access to payroll data. So you use the standard roles, and you take away the Payroll Clerk role from all of them. All good right? So you test by logging in as the user, and sure enough you still see a variety of Payroll windows. Argh.

The Payroll Clerk role definitely takes care of Payroll transactions and cards, but not setup, utilities, routines, reports, Smartlist, or inquiries. Ugh. So you go hunting, and you find that the Accounting Manager role also has a variety of payroll related windows. So you adjust to remove those tasks and/or role from the users. All good right?

Not so fast, upon logging in you find that there is one stubborn remainder...Inquiry>>Payroll>>Check History. Blah. So you review the roles one more time, nothing obvious is popping out at you. What to do?

Well, there is actually a pretty fabulous KnowledgeBase FAQ on security. You will need access to PartnerSource to access it. The FAQ contains a section on security resource descriptions. To populate the tables involved, you need to complete the following steps per the FAQ:

1. Click Microsoft Dynamics GP, point to Maintenance, and then click Clear Data to open the Clear Data window.
2. On the Display menu, click Physical.
3. In the Series list, click System.
4. In the Tables pane, click the Security Resource Descriptions table, and then click Insert.
5. Click OK.
6. Click Yes.
7. In the Report Destination window, select the Screen check box, and then click OK to send the report to the screen.
8. Close the report.

Once you have completed the steps above, you can run the following script in SQL Server Management Studio to locate the Security Roles and Tasks associated with a particular window. Note the Display Name placeholder in the last part of the script, you need to substitute the display name of the window (the name that appears across the top of the window in Microsoft Dynamics GP).

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 = ''

So, so, so easy! I ran the script for the display name 'Payroll Check Inquiry' and received a list back of a handful of GL inquiry tasks that included the Payroll Check Inquiry window. Initially my reaction was 'Why would GL inquiry tasks include Payroll Check Inquiry?' But the more I thought about it, it makes sense from a drillback perspective. Just not from a business perspective for this particular client who does not want (even GL level users) to be able to view individual payroll check information. So, with the results of the script, I was able to eliminate those tasks for the appropriate users. Then I tested a final time, and was happy to report no payroll windows when I logged in!

I know, I know, I could avoid this by defining my own roles (and perhaps tasks), but in a simple situation like that I wanted to avoid the ground up approach if possible. And the script above is just one way to troubleshoot security when you are using and/or modifying the standard roles (or even custom roles you create).

On a side note, as I was working on this issue in GP 10.0 this week, I already was missing the functionality in GP2010 that allows you to copy role assignments from user to user, and the view filters on the security tasks and roles windows to display only selected. Funny how quickly I become attached to new functionality :)