How to drop Users to assist with restoring bacpac files into Sage 200 Standard and Sage for Education
Description

When attempting to restore a bacpac into Sage for Education or Sage 200 Standard Online you may receive the following error:

  • 'Import bacpac failed’

This can be attributed to Users in the bacpac file still being associated with Roles. To resolve this we have developed a three part script to be ran against the data in SQL server management Studio which removes the Users causing the problem.

Cause
Resolution
  1. Restore the bacpac into SQL using the 'Import Data-tier Application' option.
  2. Then run the following query against the data in SQL:
    ALTER AUTHORIZATION ON ROLE::[db_execute] TO [dbo]​
  3. Then run the following query against the data in SQL:
    exec sp_dropuser 'MMSAdmin' 
    exec sp_dropuser 'MMSUser' 
    exec sp_dropuser 'MMSReader'​

The final step is to remove the following users if present - MMSReader, MMSuser & MMSAdmin. You can do this as follows:

 

  1. Within Databases in SQL Server management Studio drill down on the database in question, then drill down Security, then Users.
  2. For each of the users listed above right click and then click Delete.

You can now Export the data by right clicking the database, click Tasks, then Export Data-tier- Application.

The exported bacpac file can then be uploaded to Sage Provisioning Portal.


[BCB:180:Andy Footer:ECB]
Steps to duplicate
Related Solutions