Importing bacpac failed to Azure SQL with User, group, or role ‘XXX’ already exists

[tarashee]

Export and Import Bacpac file is one of the options available to migrate your database or to create a copy of it, like when you move your data from Azure SQL or Azure managed instance to on prem server, where backup\Restore is not supported.

This Article is an illustration of one of the issues when importing a bacpac file, if you face error similar to:

User, group, or role ‘XXX’ already exists in the current database.

Or There is already an object named ‘xxx’ in the database.

Here is the example for same scenario:

• The bacpac file was exported from a managed instance. the collation of the source database is Latin1_General_100_BIN2:

• I created two logins on Master database with different names (because the managed instance’s Master database has the default Case insensitive collation CI):

  use master
  go
  create login testbin2_1 with password = 'P@ssW0rd123'
  go
  create login TestBin2_2 with password = 'P@ssW0rd123'
  

• I mapped the logins with a new users: two users with the same name on my database (creation completed successfully):

  use testBin2
  go
  create user testbin2 for login testbin2_1
  go
  create user testBin2 for login testbin2_2
  

• If I create another database with another case sensitive collation like Latin1_General_CS_AI, I also can create the users and the same error will appear during the import process.

.

• I created the bacpac file from the Managed instance (for both databases it was succeeded):

• But if I try to import the file on Azure SQL, it will fail after creation of the destination database, even if it has been created with same case sensitive collation:

Conclusion

• On SQL Server, if you have a database with case sensitive collation, you can create objects like tables or users with the same name but with different letter case, this default behavior is not including Azure SQL Server,. Because Azure SQL is configured with other setting called “Catalog Collation”:

Catalog collation on Managed instance:

Catalog collation on Azure SQL:

For more information about Catalog collation:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations?vi...

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations?vi...

• Also,you can create a new database with catalog collation on on-prem SQL 2019 Server using the following:

CREATE DATABASE [testdb]   COLLATE Latin1_General_100_CS_AS_SC  
WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS;

But if you try  the same on managed instance, creation of the database will fail with Error:

'CATALOG_COLLATION' is not supported in this version of SQL Server.

• If you import a Bacpac file to Azure SQL and there were errors like user or table with same name already exists,  then you need to check the collation of the source database on on-prem or Managed instance servers.

• Also, you may consider to unzip the bacpac file and modify the XML files as a solution, this is to remove the elements of the issued users. As per the following article:

  https://techcommunity.microsoft.com/t5/azure-database-support-blog/editing-a-bacpac-file/ba-p/368931