Reset GP passwords after copying database

After copying a GP database from one GP instance to another, the user ids typically come along for the ride, but the encrypted passwords do not.  Here is a handy script to reset the passwords.


/*
                1. Run this script in a SQL Server Management Studio window to create output list of users

                2. Copy and paste the output list into a new SQL Server Management Studio window

                                A. review and remove from the list any user's you do not want to reset

                                B. execute the script

                                FYI: list will look like this:

                                                ALTER LOGIN djedziniak ENABLE

                                                ALTER LOGIN djedziniak WITH PASSWORD = ''

                                                ALTER LOGIN rgeorge ENABLE

                                                ALTER LOGIN rgeorge WITH PASSWORD = ''

                3. On next Logon to GP users should

                                A. Enter USERID

                                B. Click OK    (leave password field empty)

                                C. Click YES to the "Blank passwords are not allowed. Do you want to change your password now?" prompt

                                D. Enter New Password > Tab > re-enter new password > click OK

                                E. Logon to GP using the new password

C/O: Ricky George
*/
USE master
GO
SET NOCOUNT ON
DECLARE @USER_Name varchar(30)

DECLARE dbname_cursor CURSOR FOR
SELECT USERID 
FROM DYNAMICS.dbo.SY01400
WHERE USERID NOT IN ('LESSONUSER1', 'sa', 'DYNSA', 'LESSONUSER2')
ORDER BY USERID

OPEN dbname_cursor 
FETCH NEXT FROM dbname_cursor INTO @USER_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER LOGIN '+ RTRIM(@USER_name)+ ' ENABLE'
PRINT 'ALTER LOGIN '+ RTRIM(@USER_name)+ ' WITH PASSWORD = '''''
FETCH NEXT FROM dbname_cursor INTO @USER_name
END
CLOSE dbname_cursor 
DEALLOCATE dbname_cursor