Troubleshooting User Login Issues After Restoring The Dynamics GP Database To A New Server

Often times when the Dynamics GP database is restored, particularly to a lab or development environment, the individual user logins can break.  Depending on how you have the restore configured this may not be an issue, but often times I’ve noticed that many administrators tend to do manual restores of Dynamics GP to their lab or development environments.  When this is done, the security for the user logins do not always copy over correctly.

After restoring the GP back-end to a lab server, users may see the following error message when attempting to log into the system.

This is clearly a database error.  By clicking More Info you will see a more specific error message pertaining to the security currently assigned to the user.  The error message, The server principal [user] is not able to access the database “DYNAMICS” under the current security context is a pretty straight forward error (which is not always the case).

Though there are several ways to resolve this, I’ve found the simplest option is to delete the Database User account on both the DYNAMICS database and the applicable GP company database and then reassign the individual user to each database via the User Mappings page on their server login account.

The steps outlined below will walk you through the process of deleting the Database User account and reassigning the necessary security to the server login account.

The first thing you will want to do is connect into the applicable database via SQL Management Studio.  Once connected, expand the Databases drop-down and locate both the DYNAMICS database as well as your company database.  The screenshot below shows both the DYNAMICS and (in my case) the CARDF databases.

Though I will cover the necessary steps pertaining to the DYNAMICS databases, you will want to repeat these steps for your specific company database as well in order to resolve any login issues pertaining to the security not copying over correctly during the restore.

In order to remove the security profile for the individual user from the database, expand the DYNAMICS database, then expand Security.  You should now see the option for Users as shown below.

Expand Users and locate the applicable user account.  Depending on your organization, you may have a mix of GP user accounts and domain accounts.  In nearly all cases you can ignore the domain level accounts as they are likely there due to other databases on the server.

Once you have located the applicable account, right-click it and select Delete.

 

After deleting the account from the DYNAMICS database, repeat this step for the User account in your company GP database.

Once the user’s account has been deleted on both databases the last step is to reassign the account to each database on the server so that the accounts are recreated in each database with the correct security context.

To do this you will first want to collapse all all the expanded drop-downs for DYNAMICS as well as the company database if they are still expanded.  This should prevent any confusion regarding mixing up the individual database security and the server security.

Next you will want to expand the server Security drop-down as shown below.  In this example I have filtered the Logins to only show the applicable user.  In most cases you will likely see quite a few logins listed.  I recommend filtering it to avoid having to scroll through the list.

You can easily filter the list by right-clicking Logins and selecting Filter>>Filter Settings and entering the user’s login.

Once you have located the login simply right-click the login and select Properties.

The Login Properties window will be displayed.  On the left-hand side you will see a list of Pages.  Select the User Mapping page.

Under the section labeled Users mapped to this login locate the DYNAMICS database and mark the checkbox next to it as shown below.

Under Database role membership for DYNAMICS you will want to make sure to check the options for db datareader, db datawriter and DYNGRP.  All three of these roles are required in order for a successful login to Dynamics GP.

Repeat the above step for the GP company database by checking the box next to the applicable database and assigning the three roles.  

Once the roles have been assigned to both the DYNAMICS database and the company database click OK.

If you get an error message stating that the account already exists in one or more databases then it’s likely you did not delete the account from the database security prior to attempting to recreate it.  If this is the case, just go back to the database security and delete the account.  Though if you followed the steps outlined above, this shouldn’t be an issue.