Recieving CREATE USER error when attempt to import BACPAC from blob storage to ssms

Question

I am attempting to backup and restore a database located in Azure SQL database via Azure blob storage. To do this I have run Export Data-Tier Application... on the selected database and successfully stored it in a blob container as a BACPAC file. Now I am trying to do the reverse and Import Data-Tier Application... to check the backup process functions correctly, however I receive the following error during the process:

Could not import package.

Error SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.

Error SQL72045: Script executation error. The executed script: CREATE USER [username] FOR LOGIN [username];

(Microsoft.SqlServer.Dac)

This results in the Importing database, Importing package schema and data into database and Updating database operations failing, and the creation of an empty database.

I'm unsure where the login or creating a user becomes relevant in importing the database, do you know why this error is occuring?

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-09-02T11:16:36

Run this query on master. It will give you a list of logins that exist at the server level.

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.* 
FROM sys.sysusers A 
    FULL OUTER JOIN sys.sql_logins B 
       ON A.sid = B.sid 
WHERE islogin = 1 and A.sid is not null

Run this on the database you want to export as bacpac for later import it on your SQL Server instance:

SELECT DB_NAME(DB_ID()) as DatabaseName, * FROM sys.sysusers

You need to remove logins on the database that you see exist at the server level (on the master database). After that try to export the database as bacpac and import it to your SQL Server instance.

If you don't want to remove those logins/users on your current SQL Azure database, copy it as a new Azure SQL, remove logins, export it, and then drop the copied database when finish.

If you want to restore the bacpac in Azure, use the Import option on the portal instead of SSMS.

Download the latest SSMS for the best user experience.

Recieving CREATE USER error when attempt to import BACPAC from blob storage to ssms

1 Answers