A common issue that can occur when migrating a SQL database to a new/different SQL Server is an orphaned SQL login. This occurs because the login used to connect to the database does not come along with the database. Simply creating a login with the same name does not work since the underlying ID’s values are different.
To correct this issue, run the following SQL query against the database once it has been restored on the new SQL Server:
- Connect to the Promiso Server.
- Locate the <Promiso installation folder>\jsp\lib\INFOMED.properties file and open it in a text editor such as notepad.
- Locate the ODBCConnectionString property and make note of the user and password values.
- Open the Microsoft SQL Server Management Studio, connect to the database, and click the New Query button at the top.
- Be sure that the correct database is selected in the dropdown to the left of the Execute button.
- Paste the following query into the query window:
EXEC SP_change_users_login ‘Auto_Fix’,'<user name from properties file>’,null,'<password from properties file>’; - Replace the <user name from properties file> and <password from properties file> values with the values you noted during 3.
- Execute the query. The results for the query should include “The number of orphaned users fixed by adding new logins and then updating users was 1.”