Skip to content
logo Knowledgebase

How to drop Users to assist with restoring bacpac files into Sage 200 Standard or Sage 200 Extra Online

Created on  | Last modified on 

Summary

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.

Description

When attempting to restore a bacpac into Sage 200 Extra Online 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.

Resolution

  1. Restore the bacpac into SQL using the 'Import Data-tier Application' option.
  2. Run the following query against the database in SQL:
    select DBPrincipal_2.name as role, DBPrincipal_1.name as owner 
    from sys.database_principals as DBPrincipal_1 inner join sys.database_principals as DBPrincipal_2 
    on DBPrincipal_1.principal_id = DBPrincipal_2.owning_principal_id 
    where DBPrincipal_1.name = 'MMSAdmin'
  3. Then run the following query against the data in SQL:
    ALTER AUTHORIZATION ON ROLE::[db_execute] TO [dbo]
  4. 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.


Did this help?

We're always looking to improve our content based on your feedback so if you found this guide helpful, you can easily let me know by using the options below.