Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP forum
Answered

Disable sa account and rename

Posted on by 70

We have a company wide initiative to disable the sa account and rename on all instances. That would not be a problem except when you are restoring GP into a lower environment. Today we do use the sa account in the lower environment to handle the password encryption that occurs. We use sa to reset passwords.  

Is there any work around known out there for the lower environment to have sa disabled? I am assuming that all Great Plains Users including DYNSA are encrypted? We need one user to mask as sa in the lower environment Thanks for any help you can provide. 

  • PBoor Profile Picture
    PBoor 70 on at
    RE: Disable sa account and rename

    Thanks to everyone who followed this thread and responded!

  • David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,831 Moderator on at
    RE: Disable sa account and rename

    GP passwords are encrypted.

    If you use GP Power Tools to recreate them after restoring a database they will be encrypted. If you use SQL, they will not be encrypted and will need to updated again by the user.

    David

  • PBoor Profile Picture
    PBoor 70 on at
    RE: Disable sa account and rename

    Thanks, I will check out the sysadmin option. Again, i am assuming if a GP User has sysadmin rights, their password GP password that was working in prod will work in the lower environment through GP.

    Ideally, their GP password would not work from SSMS or other SQL Clients.

    Again, I am trying to solve the problem of being able to reset GP Client's passwords in a lower environment after DB restores.

  • JodeRuiter Profile Picture
    JodeRuiter 976 User Group Leader on at
    RE: Disable sa account and rename

    You can avoid using the 'sa' password to assign user passwords in the GP User Interface by assigning the sysadmin role in SQL to a GP User, your own user ID for example.  But the same issue applies that if someone obtains THAT users passwords they can do what they want in SQL.

    I would take a look at David Musgrave tools in order to control passwords in the user interface.

  • David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,831 Moderator on at
    RE: Disable sa account and rename

    You can use GP Power Tools Database Validation tool to recreate logins and create and email randomised passwords to users.

    GP Power Tools Portal: http://WinthropDC.com/GPPT

    David

  • Verified answer
    RE: Disable sa account and rename

    Hello PBoor,

    The only user that can reset passwords within GP will be the 'sa' user.

    That said, you can "blank" out passwords so that they are empty, and force users to change their passwords when they log in using SSMS and a Windows account with Sysadmin.

    Outside of this, there are multiple things that only 'sa' can do. And you will need the 'sa' user to complete any upgrades and usually to add new companies.

  • PBoor Profile Picture
    PBoor 70 on at
    RE: Disable sa account and rename

    Sorry, I am not following. My issue is after a restore into the lower environment. I am assuming all users have passwords that need to be reset in the lower environment. Your answer is telling me that if a based on SQL Server roles, I would not have an issue???

  • PBoor Profile Picture
    PBoor 70 on at
    RE: Disable sa account and rename

    How does this solve my problem of all GP users passwords encrypted in the lower environment.

  • Suggested answer
    WarrenLTenney Profile Picture
    WarrenLTenney 50 on at
    RE: Disable sa account and rename

    You can give GP users security to create new users and change passwords in GP.  This will not give the ability to create new company databases or perform some functions in Professional Services Tools.

    Add these roles to the target user within SQL Management Studio:

    Fixed Server Role

          SecurityAdmin

    Database Roles in Dynamics and each company Database

         db_AccessAdmin

         db_SecurityAdmin

     

    OR

    Using SQL query

     ALTER SERVER ROLE [SecurityAdmin]

    ADD MEMBER [<GP User ID>]

     

    USE [DYNAMICS];

    ALTER ROLE [db_AccessAdmin]

    ADD MEMBER  [<GP User ID>];

    ALTER ROLE [db_SecurityAdmin]

    ADD MEMBER [ <GP User ID>];

     

    USE [TWO];

    ALTER ROLE [db_AccessAdmin]

    ADD MEMBER  [DYNSA];

    ALTER ROLE [db_SecurityAdmin]

    ADD MEMBER [ DYNSA];

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Product updates

Dynamics 365 release plans