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
