Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Disable sa account and...
Finance | Project Operations, Human Resources, ...
Answered

Disable sa account and rename

(0) ShareShare
ReportReport
Posted on by PBoor 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,928 Most Valuable Professional 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 978 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,928 Most Valuable Professional 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
    Justin Sutton Profile Picture
    Justin Sutton on at
    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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans