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.
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.
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:
- Open SQL Server Management Studio and connect to your server.
- Open a new Query and change the context to your system database (typically DYNAMICS).
- Copy the script below into that window and run it.
- Copy the output of the script and create a new file called reset passwords.mac or something similar.
- Launch Dynamics GP and log in as either sa or DYNSA.
- Go to the User Setup window (Microsoft Dynamics GP > Tools > Setup > System > User).
- 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.
- 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
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
