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

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.

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.