Ensuring the security and efficiency of your financial management systems is paramount. Microsoft Dynamics GP, a comprehensive accounting and ERP solution, plays a crucial role in maintaining the integrity of your business operations. However, managing user access and security, particularly resetting passwords, can sometimes present challenges.

This blog delves into the intricacies of Dynamics GP user passwords, offering a streamlined approach to resetting them in mass. For businesses seeking expert guidance and support in managing their Dynamics GP system, LBMC Technology Solutions stands ready to assist. With a deep understanding of GP’s framework and a commitment to client success, LBMC is your go-to partner for all your GP support and maintenance needs.

How Microsoft Dynamics GP passwords work

When you create a user in Dynamics GP from the User Setup window (Microsoft Dynamics GP > Tools > Setup > System > User), you enter a value for the user’s password. However, when GP creates the matching login on your SQL server, it actually hashes the password using the server name in GP’s ODBC connection. This is because all GP users are made members of the DYNGRP SQL role in each database, which, in turn, gives them unfettered access to every Dynamics GP database object.

​Anyone who connected to the database using SQL Server Management Studio or even Excel, could plop in their GP user name and password and see anything they wanted to see, regardless of their security roles in GP itself. So, with a hashed password, they can’t do that because they don’t actually know what their real SQL password is.

How can you reset your user passwords in GP?

This works pretty well to secure your data, but it also creates a challenge whenever you create a test environment or move SQL servers. Because the passwords are hashed using the SQL server name, if you change the SQL server, no one except for sa (sa’s password is not hashed) will be able to log in until you reset their password in GP, one at a time.
​
However, GP has the capability to run macros and, using SQL Server Management Studio, we can generate a macro to reset the password for all GP users (that also actually have a SQL login).
​
I’m going to stop and add a quick disclaimer before going any farther. You should ALWAYS run a backup before running any scripts. If you are not comfortable performing a backup or running SQL scripts, it is best to contact your partner or Microsoft directly. Every environment is different so these scripts are provided as is, without warranty.

Follow these steps:

  1. Open SQL Server Management Studio and connect to your server.
  2. Open a new Query and change the context to your system database (typically DYNAMICS).
  3. Copy the script below (scroll down) into that window and run it.
  4. Copy the output of the script and create a new file called reset passwords.mac or something similar.
  5. Launch Dynamics GP and log in as either sa or DYNSA.
  6. Go to the User Setup window (Microsoft Dynamics GP > Tools > Setup > System > User).
  7. Without moving the cursor out of the first box (macros are pretty sensitive, so your cursor has to be in the right place at the beginning), from the window ribbon select Tools > Macro > Play.
  8. Navigate to wherever you saved the macro you created in step 4 and click open.

In addition to setting their password to the temporary value, it also forces them to change it when they log in, so you don’t have all users with the same password in your test system.

Script:

​declare @macro nvarchar(max), @userid nvarchar(50), @enforcePasswordPolicy bit, @enforceExpiration bit
declare users cursor for
    select
        rtrim(USERID),
        l.is_policy_checked,
        l.is_expiration_checked
    from
        sy01400 u with (Nolock)
        inner join sys.sql_logins l with (nolock) on l.name=u.USERID and l.type=’S’
    where
        userid not in (‘sa’,’DYNSA’)
        and SQLLoginID <> ”

print(‘CheckActiveWin dictionary ”default”  form ”Enter_User_Names” window ”Enter_User_Names” ‘)
open users
fetch next from users into @userid, @enforcePasswordPolicy, @enforceExpiration
while @@fetch_status <> -1 begin

    set @macro = ‘  TypeTo field ”User ID” , ”’ + @userid + ”’
MoveTo field ”User Name”
MoveTo field Password
TypeTo field Password , ”L3tmein!”
MoveTo field ”(L) Confirm Password”
TypeTo field ”(L) Confirm Password” , ”L3tmein!”’

if  @enforcePasswordPolicy=0 begin
    set @macro = @macro + ‘
MoveTo field ”(L) Enforce Password Policy”  # ”FALSE”
ClickHit field ”(L) Enforce Password Policy”  # ”TRUE”’
end else begin
    set @macro = @macro +
‘
MoveTo field ”(L) Change Password Next Login”  # ”FALSE”
ClickHit field ”(L) Change Password Next Login”  # ”TRUE”’
end

set @macro = @macro + ‘
MoveTo field ”Save Button”
ClickHit field ”Save Button”’
    print(@macro)

    fetch next from users into @userid, @enforcePasswordPolicy, @enforceExpiration
end

close users
deallocate users

Navigating the complexities of Microsoft Dynamics GP, especially when it comes to resetting user passwords, can be a daunting task for businesses. The method outlined above provides a fast and efficient way to address this challenge, ensuring your team can access the system without unnecessary delays.

However, it’s crucial to proceed with caution and consider professional assistance to avoid potential pitfalls. LBMC Technology Solutions offers unparalleled expertise in Dynamics GP, providing the support and maintenance services your business needs to thrive. Whether you’re setting up a new test environment, migrating servers, or simply looking for ongoing GP support, LBMC is here to help. Don’t let technical hurdles slow down your business operations. Contact LBMC Technology Solutions today for expert GP support and ensure your financial management system is in capable hands.