So if you work with Dynamics GP in any form of administrative role you understand the cost associated with a user license for your company. So what I like to do is for routine task in Dynamics is to either create a SSRS item or use a SQL script to gather information.
One of the task I often get asked is who has what security roles inside of Dynamics GP. So instead of logging into GP and drilling down through the embedded reports or going user by user in the security setup window this script will display that information. I will be releasing another file soon that can be tied to an existing SSRS deployment so you can run it as a report and then I’m going to build a version that plugs in as a custom report for SSMS. I just haven’t had time to really set down and build the yet!
One last note is that this must be run against the DYNAMICS database. Since GP 2013 allows the use of any name for the DYNAMICS database I didn’t bother to include the USE DATABASE part of the script.
GP Security Role Assignment – This will take you to the source of the script.
BY Jonathan P. Cox http://www.jonathanpcox.com @HACKDBA
This script will display the list of all users in Dynamics and their associated security roles.
SELECT SY10500.USERID AS GP_USER, SY10500.CMPANYID, SY10500.SECURITYROLEID, SY09100.SECURITYROLENAME, SY09100.SECURITYROLEDESC,
SY09100.SECROLETYPE, SY01500.CMPNYNAM AS Company
FROM SY10500 INNER JOIN
SY09100 ON SY10500.SECURITYROLEID = SY09100.SECURITYROLEID INNER JOIN
SY01500 ON SY10500.CMPANYID = SY01500.CMPANYID
GROUP BY SY10500.USERID, SY10500.CMPANYID, SY10500.SECURITYROLEID, SY09100.SECURITYROLENAME, SY09100.SECURITYROLEDESC, SY09100.SECROLETYPE,
ORDER BY GP_USER