3,815
I used to have a script that would blank out all the user's passwords so that when they log in, it prompts them to setup a new one. I migrated from a SQL 2005 server to a new SQL 2012 server then upgraded, so the server names are different.
Why is it not possible to run a sql script on the SY01400 table to blank out all the passwords? We have over 60 and logging in as sa, as is documented by Microsoft, is crazy.
*This post is locked for comments
This was a huge time saver so I cleaned up the script and made it a cursor. Enjoy
DECLARE BlankPwds CURSOR FOR
SELECT RTRIM(USERID) FROM DYNAMICS..SY01400 WHERE USERID NOT IN ('sa', 'DYNSA')
OPEN BlankPwds
DECLARE @USERID VARCHAR(15);
DECLARE @SQL VARCHAR(75);
FETCH NEXT FROM BlankPwds INTO @USERID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @SQL =
'ALTER LOGIN [' +@USERID + '] WITH PASSWORD = '''', CHECK_POLICY = OFF'
EXEC (@SQL)
FETCH NEXT FROM BlankPwds INTO @USERID
END
CLOSE BlankPwds
DEALLOCATE BlankPwds
This is GREAT! However, I would recommend you include userid not in ('sa', 'DYNSA')
You don't want to have blank DYNSA
Rich, THANKS SO MUCH! This worked but I had to run the query, then take the results, copy and paste them in to a new query window and run them. Now when my users log in, they are greeted with the message that blank passwords are not allowed and they have to reset.
Sherri
This script will create an output script which sets a blank password. When the GP user logins in to GP with a blank password GP then forces the user to set the password.
-- Resets SQL passwords to blank based on Login ID's in the DYNMAICS..SY01400 table
select 'Alter login [' + rtrim (USERID) + '] with password = '''', CHECK_POLICY = OFF' AS [Run to reset password to blank] from DYNAMICS..SY01400 WHERE USERID not in ('sa')
Rich
Hi,
I would probably just write a macro to go through and set the passwords. Do you know what the old passwords were? You could easily create a macro using mail merge to re-enter them.
Kind regards
Leslie
I am not sure how this would have worked in previous versions because we haven't changed how we encrypt the passwords. At this point the only supported way is to have SA change their passwords. I am sure you could find other ways online but from a Microsoft standpoint this is the only supported way.
Yes that's the KB I used adn that's the one that says you have to manually setup the passwords with sa. Actually prior versions allowed you to blank out each user's passwords with a sql script so when they logged in, Dynamics prompted them to setup a password. It worked beautifully in versions 8, 9, 10.
"Note If the old server was running Microsoft Dynamics GP and does not have the same name as the new server, the passwords for the users will no longer be valid. To reset the password, follow these steps:
1.Log on to Microsoft Dynamics GP as the sa user.
2.On the Tools menu, point to Setup, point to System, and then click User.
3.Click the Lookup button next to User ID and select the appropriate user.
4.In the password field, enter a new password, and then click Save"
The above step for 50+ users is crazy.
This information is in KB878449 which you should be using to move the databases. Have you reviewed that KB yet?
The reason is because GP encrypts the passwords based off the SQL instance name. If you are moving to a new SQL instance that has a different name the encryption is not going to work. This is how GP has always been and will always be.
So we migrate to a new server and upgrade and we're stuck with none of our users being able to login and this is by design?? The only option avialable to us it to log in as the sa and assign everyone a password and hope that they go in and change their password? Sorry but this doesn't seem secure at all and rather silly to have a system admin assign people their passwords. That is going to take hours.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (