Quickly Add Users to POWERUSER Role in Dynamics GP 2010

 
/*
AUTHOR: AARON BERQUIST - WWW.AARONBERQUIST.COM
DATE: 11/25/2010
PURPOSE: THIS SCRIPT WILL ADD EVERY DYNAMICS GP USER TO THE POWERUSER ROLE, IN FABRIKAM, INC.
IT WILL ALSO SET THEIR ALTERNATE/MODIFIED FORMS AND REPORTS ID TO DEFAULTUSER
USAGE: USE THIS SCRIPT TO QUICKLY SET UP USERS FOR ACCESS TO A TEST OR DEMO SYSTEM
 
TESTED ON DYNAMICS GP2010, BUILD 11.00.1524 (SP1)
*/
 
USE DYNAMICS
GO
--PROCESS THE ROLES
INSERT INTO SY10500
	(USERID,
	CMPANYID,
	SECURITYROLEID)
SELECT
	sy4.USERID,
	-1, --THIS IS THE CMPANYID FOR FABRIKAM (AKA TWO),
	'POWERUSER' --FOR THE PURPOSES OF THE DEMO SYSTEM, ALL USERS HAVE FULL RIGHTS TO THE APPLICATION
FROM
	--USER TABLE
	SY01400 sy4
	LEFT JOIN SY10500 sy5 on sy4.userid = sy5.userid
WHERE
	sy5.USERID IS NULL 
	--WE DO NOT WANT TO TOUCH THE "SA" ACCOUNT IN ANY WAY
	AND sy4.USERID <> 'sa' 
GO
--PROCESS THE AFA'S
INSERT INTO SY10550
	(USERID,
	CMPANYID,
	SECMODALTID)
SELECT
	sy4.USERID,
	-1, --THIS IS THE CMPANYID FOR FABRIKAM (AKA TWO),
	'DEFAULTUSER' --FOR THE PURPOSES OF THE DEMO SYSTEM, ALL USERS WILL HAVE THE DEFAULTUSER AFA
	FROM
	--USER TABLE
	SY01400 sy4
	LEFT JOIN SY10550 sy5 on sy4.userid = sy5.userid
WHERE
	sy5.USERID IS NULL 
	--WE DO NOT WANT TO TOUCH THE "SA" ACCOUNT IN ANY WAY
	AND sy4.USERID <> 'sa'