Microsoft SQL Server

  1. In Architect, check in and release the database. This ensures that the production and authorized databases are synchronized.
  2. Create a complete backup of the source production and authorized databases.
  3. On the destination database server, add SQL logins for all the users.
  4. Use SQL database Detach/Attach or Backup/Restore procedures to move the production and authorized databases, copying the files for the source production and authorized databases to the destination database server.
  5. Synchronize the database users for the ported production and authorized databases using the stored procedure, sp_change_users_login. You can use the EXEC SP_CHANGE_USERS_LOGIN report command to see a list of users that may be out of sync. You can find further details on using this procedure in Microsoft's help or by accessing docs.infor.com.
  6. Each SQL Server login must be assigned to a default database to which the user can validly connect. You can verify this in Microsoft SQL Server Management Studio.
  7. Optionally, as good practice, you can specify the production database as the default database for each user login.

If you have problems with user security after following the previous steps, it c be that running the stored procedure, sp_change_users_login, did not fully synchronize the user_ids for the users with the uids in the sysusers table in the database.

The user_ids need to be synchronized in a number of tables. We recommend that you contact customer support for a general script to provide you with a starting point that you can customize as needed.

These user and user security tables are involved:

  • BPR_USERS
  • BPR_USER_SECURITY
  • NOTE_DETAIL_BASE
  • UNIT_SECURITY_ACCESS
  • UNIT_SECURITY_RW
  • <SecuredDimension*>_SECURITY_ACCESS
  • <SecuredDimension*>_SECURITY_RW

* If your application has security assigned to multiple dimensions, you must synchronize the user_ids in the SECURITY_ACCESS and SECURITY_RW tables associated with each secured dimension. The Unit dimension is always a secured dimension.