Troubleshooting Dynamics GP role based security

Troubleshooting the new role-based security in Dynamics GP 10 and Dynamics GP 2010 can be tricky because each role has access to one or more of hundreds of security tasks. Each security task grants someone access to a specific function in Dynamics GP. Most importantly, the security tasks can overlap: to properly take away someone's access to a specific function, you may need to tweak several security tasks.

Sometimes, you need to know exactly which security tasks are involved with giving a user access to a particular screen. You can run a SQL query on the database. Here's how:

Step 1 - Perform this setup step.

Per Microsoft kbase article 951229, you must first populate a database table with your current security settings.

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.

The Security Resource Descriptions table is populated. You can use the table in an SQL query in Microsoft SQL Query Analyzer or in Microsoft SQL Server Management.

Step 2 - Run this sql 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.SECURITYID

FROM DYNAMICS.dbo.SY09400 R

FULL JOIN DYNAMICS.dbo.SY10700 O ON R.DICTID = O.DICTID

AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID

FULL JOIN DYNAMICS.dbo.SY09000 T ON T.SECURITYTASKID = O.SECURITYTASKID

FULL JOIN DYNAMICS.dbo.SY10600 A ON A.SECURITYTASKID = T.SECURITYTASKID

FULL JOIN DYNAMICS.dbo.SY09100 M ON M.SECURITYROLEID = A.SECURITYROLEID

WHERE R.DSPLNAME = 'Sales Transaction Entry'

Change the "sales transaction entry" to whatever the display name is of the window you are troubleshooting.