Open Forum

Expand all | Collapse all

Refreshing test database server from production - security

  • 1.  Refreshing test database server from production - security

    Posted Aug 23, 2019 12:20 PM
    Since our UAT database was originally refreshed with production data, we have created new security roles in production and deleted the old roles.  Since we did that, we have only refreshed our COMPANY UAT database from production but not our DYNAMICS database.  So the new security roles do not exist in UAT.  Manually creating the new security roles in UAT (and 3 other non-production environments) would be a significant and tedious task.  Sometimes in the non-production environments we have users for which I can't reset passwords or make any security changes without getting various errors from the GP Users window.  When that happens, the DBA works some magic to get the users to be able to login.  I assume this is related to refreshing databases that don't have all of the same users in them.

    So what are the downsides to refreshing the UAT DYNAMICS database from production?  If we do this, will we then have only the new security roles in UAT?  Will we get the users' custom home pages in UAT?  Assuming production contains our preferred users, will they be able to login to UAT?  Will their passwords have to be reset?  Will we need to create SQL users if they weren't already on the UAT server?   Are there other issues we should consider?

    Thanks in advance for any answers or advice you can give.


    ------------------------------
    Randy Johnston
    Business Systems Analyst, Accounting
    Optum Workers' Comp and Auto No-Fault
    Westerville, Ohio
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: Refreshing test database server from production - security

    TOP CONTRIBUTOR
    Posted Aug 23, 2019 02:52 PM
    There are scripts that you should run after restoring the database,

    Try this GPUG discussion blog.

    Or ask Dr Google something like: Dynamics GP restore security roles

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: Refreshing test database server from production - security

    TOP CONTRIBUTOR
    Posted Aug 23, 2019 02:54 PM
    Or better, ask Dr Google about:

    dynamics gp restore test company from production company

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: Refreshing test database server from production - security

    GPUG ALL STAR
    Posted Aug 26, 2019 01:49 AM
    Hi Randy

    You might find this article useful:

    How to transfer Security Tasks and Roles between Systems

    Also, using GP Power Tools' Database Validation can recreate all the logins and database users on your UAT system for you.

    Regards

    David

    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Conference-GPUG_200x200


  • 5.  RE: Refreshing test database server from production - security

    GPUG ALL STAR
    Posted Aug 27, 2019 07:24 PM
    Edited by Beat Bucher Aug 27, 2019 08:37 PM
    Hi @Randy Johnston,
    The 1st link that @David Musgrave provided will give all the steps to transfer the new security tasks & roles you created in your UAT server..
    The trick is that the GP passwords are tied to the server name that you use in the ODBC link that gets created during the installation of the GP client..

    Example : if your PROD GP clients are pointing to SERVER1\DYNGP SQL instance, then this name will be used to encrypt the GP user's password.
    Now, if your UAT SQL server instance is called UATSRV\DYNGP, then obviously the login with the same password into GP will fail, because the key to decrypt the pwd is no longer the same..​​​​

    That's where the GP PowerTools comes in very handy, as you don't have to worry about that part, since it includes a function to reset (and email when possible) the password to all GP users (part of the Database Validation module). It also has the capability to simply recreate all your missing SQL users in the security on the new server when you create a new UAT environment from scratch, something you had before to run a SQL script provided by Microsoft in a KB article to gather all the credentials, and it was including everything, not just GP users... not always what you want.

    Every time you refresh your DYNAMICS system DB in the UAT from the PROD, you'd overwrite all your security and the logins of the users that is stored (encrypted) in the SY01400 table. If all you're looking is to update your UAT environment with the new Roles & Tasks you created, then the steps give above in David's blog is the way to go. Else, a full refresh approach requires to reset all passwords, for which the most recent versions of GP have a function called "force all users to change Password", but it has no way like GPPT to randomly generate a new password and e-mail it to the users directly (or print a report).

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role