Sage 100

Upgrade from 2013 Premium to v2016 SU2 Premium. Have custom reports attached to Sage 100 custom report menus. Reports run fine outside of the menu launched direct from a Crystal report file and also run fine in at least one ""test"" company but all fail in the production database. The error is ""The database login failed. Failed to open the connection. Details: Database Vendor Code:4060 Failed to open the connection <report name>. Database Vendor Code:4060."" Sage says they cant help because they are custom reports. Its not the MAS_Reports password because it would not run from the test company otherwise. I did notice in SQL Mgmt. Studio under Security>Logins>MAS_Reports, Rt. click Properties > User Mapping the ""Map"" check box for the production company is not checked and is checked for the test company that works, however when I select it to try and update it I get an SQL error that says ""User, group, or role MAS_Reports already exists in the current database. No I cant just have then use the the test company as production moving forward, anyone have any idea what this issue is or how to fix it? HELP!

use MAS_###; exec sp_dropuser 'MAS_Reports'; exec sp_grantdbaccess 'MAS_Reports'; exec sp_addrolemember 'db_datareader', 'MAS_Reports';

@BruceKern did Kevin's suggestion work? I want to get it in our 90 Minds KB if so.

I did not try Kevin's fix because I was not sure if these were queries or how to apply the suggestion. What I did to resolve this per another suggestion was to give MAS_Reports the server roles: public, serveradmin, setupadmin, and sysadmin and seems to be working. Do you have any more on how to implement Kevin's idea?

When you restore database backups from one instance to another, the previous ""MAS_Reports"" SQL login permission comes along for the ride, but the ID for the login will not match between the two instances. This means that you need to remove the old permission, then re-add it, so the SQL login for the current instance has it's ID# used for the settings. The same issue applies to database ownership, but since you didn't complain about not being able to work with the copied data, I omitted the part of the script that sets MAS_User as the db owner.

(Since you probably created your test company after the migration, the SQL permissions would be created new, properly, without the inherited database permissions from the old SQL server instance).