Dynamics GP Export / Query User Security Roles

Export.sql

-- This can be used for GP 2010, GP 2013, GP 2015 and GP 2016.

use DYNAMICS;

SELECT
	UserMaster.USRCLASS AS 'User Class',
	UserMaster.USERID AS 'User ID',
	UserMaster.USERNAME AS 'Username',
	CASE UserMaster.UserStatus
		WHEN 1 THEN
			'Enabled'
		ELSE
			'Disabled'
		END AS 'Status',
	CONVERT(DATE, UserMaster.MODIFDT) AS 'User Modified Date',
	CONVERT(DATE, UserMaster.CREATDDT) AS 'User Created Date',
	RoleMaster.SECURITYROLEID AS 'Security Role ID',
	RoleMaster.SECURITYROLENAME AS 'Security Role Name',
	RoleMaster.SECURITYROLEDESC AS 'Security Role Description',
	ISNULL(CompanyMaster.INTERID, '') AS 'Company ID',
	ISNULL(CompanyMaster.CMPNYNAM, '') AS 'Company Name'
FROM
	SY01400 AS UserMaster
	JOIN SY60100 AS UserCompanyAccess ON UserCompanyAccess.USERID = UserMaster.USERID
	JOIN SY01500 AS CompanyMaster ON CompanyMaster.CMPANYID = UserCompanyAccess.CMPANYID
	JOIN SY10500 AS RoleAssignment ON RoleAssignment.USERID = UserMaster.USERID AND RoleAssignment.CMPANYID = CompanyMaster.CMPANYID
	JOIN SY09100 AS RoleMaster ON RoleMaster.SECURITYROLEID = RoleAssignment.SECURITYROLEID
ORDER BY
	UserMaster.USERID