tracking code
LBMC Technology Solutions
  • Home
  • Solutions
    • Dynamics 365
    • Dynamics NAV
    • Dynamics GP
    • Dynamics CRM
    • Sage Intacct
    • Document Management (ECM)
    • Network Engineering and IT Solutions >
      • IT Security
      • Managed IT
      • Business Phone Systems
  • Events
    • All Upcoming Events
    • Training / CPE
    • Webinars and Workshops
  • Resources
    • Blog
    • Forum
    • On-Demand Webinars
    • Referral Program
    • Services We Offer
    • Support
  • About Us
    • Careers
    • Partner with Us
    • Community
    • Contact Us

The fastest way to reset all Dynamics GP user passwords

1/5/2016

 
Posted by Paul Haag

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

Interested in Microsoft Dynamics GP training?
View other services we offer

Comments are closed.


    Blog

    ​Your one stop shop for technology.

    Categories

    All
    Business Central
    Dynamics 365
    Dynamics CRM
    Dynamics GP
    Dynamics NAV
    Events
    Microsoft Dynamics Add-ons
    Software Decisions
    Tutorial

Solutions
Microsoft Dynamics 365
    Business Central
    Customer Engagement
Microsoft Dynamics NAV
Microsoft Dynamics GP
​Microsoft Dynamics CRM
​Sage Intacct
​
Network Engineering

​ERP Comparison Guide
Learning Opportunities
All Upcoming Events
User Conferences
Training / CPE
Vision
Webinars & Workshops
​

Resources
Blog
Forum
Referral Program
​​YouTube Channel

Company
About Us
Careers
Contact Us
​
Locations
Charlotte
Knoxville
Nashville
Picture
Picture
Proudly powered by Weebly

Sitemap | Home | Careers | Contact Us

share

Copyright © 2019

LBMC Technology Solutions