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.
ACE Micro Blog Site 