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

When creating a company database in Sage 200 based on a bacpac file, you may come across an error, the script in this article resolves the problem.

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.

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.

---