This particular error is one I have seen a number of times after a client, or inexperienced consultant, has copied a database from the live server to the test server and run the scripts from the Microsoft Knowledge Base and thought everything was fine after logging in with the sa account (which is another good reason for not using sa as it hides a multitude of errors.
However, as soon as a user tries to log in, they receive the error message shown below:
Microsoft Dynamics GP A get/change operation on table 'syUserDefaults' failed accessing SQL data.
The cause of this error is quite simple; the SID (Security Identifier) of the Server Login ID and of the Database User are different.
There are two ways of fixing it:
- Use SQL Server Management studio (SSMS) to delete the server login and database users and then save the user with a new password from within Microsoft Dynamics GP.
- Use SSMS to run an ALETR USER command on the database user to link it to the Server Login.
The latter option is by far the quicker, and there are two methods you can use to do this:
- Alter each user individually, which is only feasible if you have a small number of users.
- Alter all users in Microsoft Dynamics GP using a SQL script, which is by far the easiest method.