ACEConnect Interation Templates for Dynamics GP

GP 2010 Security Report

I received a request from a client who needed to supply a GP User Security Report to their auditors. Prior to GP version 10, we were at the mercy of the GP Report Writer report that was typically several thousands to several tens of thousands of pages long. We would usually modify the report to print one line per record to save space and so that it was easily exported, but even that produced a very large file. Now with the new Role-based Security in GP 10 and 2010, we have access to this data in SQL. I developed the script below and thought that others in the GP community might find it useful. It provides a nice, simple view of the user security in GP. It does not drill down to the specific window level, but it has every user, the companies they access, the roles they are assigned to in each company and the task and task descriptions within each role. Pretty sweet. I hope you guys can use it someday.

The script:

Use dynamics

select rol.userid, cmp.interid, rol.securityroleid, tsk.securitytaskid, td.securitytaskname, td.securitytaskdesc

from SY10500 rol

inner join SY01500 cmp on rol.cmpanyid=cmp.cmpanyid

inner join SY10600 tsk on rol.securityroleid=tsk.securityroleid

inner join SY09000 td on tsk.securitytaskid=td.securitytaskid

order by rol.userid, cmp.interid, rol.securityroleid, tsk.securitytaskid

A sample result set:




Tonya Boyce

ACE Microtechnology, Inc.

www.acemicrotech.com

tboyce@acemicrotech.com

Corporate Environments Microsoft Dynamics CRM 2011 Online