Disable sa account and rename

(0) Share

Report

Posted on by PBoor

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.

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

WarrenLTenney 50 on at

Like (0)

Report

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];

Was this reply helpful? Yes No
PBoor 70 on at

Like (0)

Report

RE: Disable sa account and rename

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

Was this reply helpful? Yes No
PBoor 70 on at

Like (0)

Report

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???

Was this reply helpful? Yes No
Suggested answer

Justin Sutton on at

Like (0)

Report

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.

Was this reply helpful? Yes No
David Musgrave MVP ... 14,069 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
JodeRuiter 980 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
PBoor 70 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
David Musgrave MVP ... 14,069 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
PBoor 70 on at

Like (0)

Report

RE: Disable sa account and rename

Thanks to everyone who followed this thread and responded!

Was this reply helpful? Yes No