Aaron Berquist Rotating Header Image

User Setup

Quickly Add Users to POWERUSER Role in Dynamics GP 2010

One of the more tedious processes in configuring a “demo” or “test” system is setting up user accounts. Recently, I needed to configure a GP 2010 “Sandbox” for some internal users who wanted to look at the new features Dynamics GP 2010 has to offer.

After manually creating their accounts (Dynamics GP: Tools: Setup: System: User Setup), and granting access to the Fabrikam company (Dynamics GP: Tools: Setup: System: User Access), I wrote a quick script to add all users to the POWERUSER role, with the DEFAULTUSER Alternate/Modified Forms and Reports ID.

The script adds records to the SY10500 and SY10550 tables for all users in the SY01400 table, excluding “sa”:

 
/*
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'

Download the script here: GP 2010 – Configure Users for Demo System.sql

Post to Twitter