After restoring a production company, I am unable to add existing users to this company. Error $The user could not be added to one or more databases.$
This is GP 10
*This post is locked for comments
Thank you, this worked for me-
Test company data gets copied over from production database on same server. Was attempting to add user back to test company and was getting above error. Unchecking user mapping on SQL server allowed user to be re-added to company via GP.
No, this was not copied from another server. It was a pre year end close backup of one of out 4 companies that was restored after the company had been closed.
I was able, in SQL Server Security=>Logins=>User Mapping, to uncheck the Map checkbox on the restored company. After doing that the User Access function in GP allowed me to add the users to the company.
If I may interject a queston here, was this copy of the production company database copied from another server? If so, you really need to run the CaptureLogins script on the source server and run the results on the destination server. Here is probably what is happening.
Server A has user Susan, Karen and BIll
You created the production database and gave these 3 users access to this company so now these 3 users have logins to this company. Keep in mind that user accounts get created inside the DYNAMICS database as well as the SQL instance at the same time.
Now lets say you go to your destination server with a fresh copy of GP and it own DYNAMICS database and an empty copy of your production database. You now do a restore of the production database on the destination server. You now have created two problems.
1) The users from Server A do not exist in the DYNAMICS database user table
2) The users from Server A do not exist as valid logins in the destination instance of SQL.
So you go into GP on the destination server as 'sa' and try to add these users and give them access to the production company. The scripts will fail because the users are in the production user database but not in the sql instance logins of the destination server. You will not be able to remove them either because of the same reason.
It is best to follow the instructions in KB 878449 when you are doing a server move.
If I have missed the mark on what happened here please accept my apologies.
Yes, when you add a new user to GP, it creates a Server login and gives it access to DYNAMICS. As you go through the "User Access" check boxes, you actually are adding that login for permission to access that database (with DYNGRP role). Are you going from one server to another? Because that can sometimes be a little trickier.
Here is on scanario that might help explain it. I will have a user that has only access to the Test company. Call it TEST. But no access to PROD. DYNAMICS says this user has access to TEST. When I take a copy of PROD and restore it to TEST, all of PROD's logins are copied too. I try to log in as this user, pick my company TEST, and get an error message like, "User XXX cannot access company TEST under the current security context...." or something like that. DYNAMICS thinks that the user has access (box is checked in User Access to that company) but there is no login under the TEST database anymore for user XXXX, because there was not one for PROD which you restored from. You would have to uncheck TEST for that user in company access, then just check it again, and it will add their login back to TEST. You'll have to redo security for that user though.
You could also just go into TEST and add the login in SQL XXXX to that database, grant DYNGRP and it would probably work as well. Just tricking GP.
So each database has security and logins which is different from the Server Login. Look for that login (GP User ID) on the actual company database. Toggle the check boxes in User Security sometime and see it remove and add logins to the individual companies.
Actually I restored the same company back as itself after a closing entry was found to be incorrect after year end close. There are multiple active users and multiple active companies that are still working.
Can you delete logins from one company and not affect logins in the other companies?
This usually has to do with logins at the database level. Meaning that either GP UserID has database access to that company and Dynamics doesn't think so or the other way. You copied a company with one database ID over to a different database ID, right? That also copies logins with it. So say you had one DB called PROD and one called TEST. PROD gets copied with all of the logins too. Just delete these logins from that database. I think GP gets confused when it tries to add the login, but it already exists at the database level.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,151 Super User 2024 Season 2
Martin Dráb 229,993 Most Valuable Professional
nmaenpaa 101,156