Error message when you try to grant a user access to a test company in Microsoft Dynamics GP: "The user could not be added to one or more databases"

Dynamics GP all versions

Symptoms

When you try to grant a user access to a test company in Microsoft Dynamics GP you receive the following error message:

The user could not be added to one or more databases.

Additionally, if you are collecting a Dexsql.log the following information is logged in the Dexsql.log:

[Microsoft][ODBC SQL Server Driver][SQL Server]User, group, or role 'XXXX' already exists in the current database.*/

Cause

This problem occurs if the user already exists in the test company database.

Resolution

To resolve this problem, follow these steps:

1. Delete the user from the test company database. To do this, follow the appropriate steps.
   
   Using the latest SQL Server Management Studio 18 follow these steps:
   1. Click Start, point to All Programs, point to Microsoft SQL Server Tools 18 and then click SQL Server Management Studio.
   2. In the Server namebox, type the name of the instance of SQL Server.
   3. In the Authenticationlist, click SQL Authentication.
   4. In the User namebox, type sa.
   5. In the Passwordbox, type the password for the sa user, and then click Connect.  Steps a thru d should look like this
   6. In the Object Explorerarea, expand Databases, expand the test company database, expand Security, and then expand Users.
   7. Right-click the user in question, and then click Delete.                               
   8. Verify that the information is correct, and then click OK.

2. Grant access to the test company for the user. To do this, follow the appropriate steps.

Start Microsoft Dynamics GP

1. Log on as the sa user.
2. On the Microsoft Dynamics GPmenu, point to Tools, point to Setup, point to System, and then click User Access.
3. Click the user in question, click to select the Accesscheck box, and then click OK to grant access to the test company that has live data.

More Information

When a user is granted access to a test company, the user is saved to the SYSUSERS table in the company database. Then, a record of this event is saved to the SY60100 table in the DYNAMICS database.

When the live company database is backed up, the SYSUSERS table is saved together with all the other tables in the live company database. This backup is then restored into the test company. Additionally, the SYSUSERS table is restored into the test company together with all the other tables in the backup.

When you try to grant the user access to the test company, Microsoft Dynamics GP tries to add the user to the SYSUSERS table. In this situation, you receive the error message that is mentioned in the "Symptoms" section because the user already exists in this table.