Match SQL Access to GP Access

We recently reformatted an 80-company Dynamics GP installation using Corporate Renassaince Group's Reformatter. The Reformatter migrates to a second SQL server, data is bulk copied over, and the accounts are re-formatted in the process. Works great. But...

Two things you have to do yourself: set up companies in GP Utilities on the new server for each company in your installation, and set up SQL logins and give them access to each database in SQL. I used the KB878449_Capture_Logins.sql script and reset the user passwords, but I still had to manually add them to the same SQL databases they had on the existing server. Imagine doing that for 10 users times 80 companies!

Well, that's why I created the script below, but it has other applications as well. Have you ever created a test company, copied the live data over, went into GP's User Access window, and checked the Access box for that new company only to get "The user could not be added to one or more databases"? This script is for you. It will also remove users from databases to which they should not have access.

Other errors this script may fix (depending on the root cause) include "A get/change operation on table 'syUserDefaults' failed accessing SQL data.", "The server principal (login) is not able to access the database (database) under the current security context.", and "A get/change first operation on table 'coProcess' failed accessing SQL data."

/*
This script will set access to SQL databases where each GP user has access
  within the User Access window inside GP.
You can run this after setting up a test company, or any time you get the error:
  The user could not be added to one or more databases.
*/
declare @DatabaseId    varchar(5),
        @UserId        varchar(30),
        @NotFound      tinyint,
        @SQL           varchar(max)
set     @SQL = ''
declare UserLoop cursor for
    select USERID from DYNAMICS..SY01400
        where USERID NOT IN ('sa','DYNSA')
open UserLoop
fetch next from UserLoop into @UserId
while (@@fetch_status <> -1)
    begin
        SET @SQL = @SQL + 'USE [DYNAMICS]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
    fetch next from UserLoop into @UserId
    end
close UserLoop
deallocate UserLoop
declare DatabaseLoop cursor for
    select RTRIM(c.INTERID),RTRIM(u.USERID),(CASE WHEN a.USERID IS NULL THEN 1 ELSE 0 END) NotFound
    from DYNAMICS..SY01500 c cross join DYNAMICS..SY01400 u left join DYNAMICS..SY60100 a ON c.CMPANYID = a.CMPANYID AND u.USERID = a.USERID
    WHERE INTERID IN
        (select name from master..sysdatabases)
    AND u.USERID NOT IN ('sa','DYNSA')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId,@UserId,@NotFound
while (@@fetch_status <> -1)
    begin
    /*Inner Loop for each Database*/
        IF @NotFound = 0 -- it is found; add it if it does not exist
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
        ELSE
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    DROP USER [' + @UserId + ']
'
    fetch next from DatabaseLoop into @DatabaseId, @UserId, @NotFound
    end
close DatabaseLoop
deallocate DatabaseLoop
exec (@SQL)