2

I am trying to import a .bacpac of a local sql database to my Azure SQL Server using SSMS, but it fails with the message

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 execution error. The executed script: CREATE USER [abc] FOR LOGIN [abc];

As far as I am aware, the login I am using is the server admin account and is the only account on the server. Is the problem created by the fact that I have a user in the database with the same name at the server admin account? Can anyone explain what I am doing wrong here?

Alistair67
  • 59
  • 11

1 Answers1

0

Could you please change the owner on the database on-premises to “sa” and generate the bacpac again?

My recommendation is to use Data Migration Assistant instead of importing a bacpac. It is the easiest way to migrate databases to Azure SQL Database. You can download it here.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Tried to do what you suggest, but Migration Assistant won't connect to my Azure server, saying "There are validation errors in the target server." I am using SQL authentication and giving the server admin username and password. – Alistair67 May 18 '18 at 11:58
  • Ok, got it to connect but in step 2 of deploy results it fails to create the sa user because `The login already has an account under a different user name.` – Alistair67 May 18 '18 at 12:39
  • How was I supposed to change the owner on the database to sa - I just renamed the owner account. Was that wrong? – Alistair67 May 18 '18 at 12:43
  • Change the owner to another login instead of sa – Alberto Morillo May 18 '18 at 12:46
  • If I right-click on the database in SSMS and look at the properties the owner is listed as my Windows user, if I look in the security folder of the database at the sa user it is listed as owning the db_owner schema (which I thought made it the database owner). Not sure which to change or how to do it. Sorry for my ignorance. EDIT: I just worked out how to change the owner of the db_owner schema, which I've done. Will try again to migrate. – Alistair67 May 18 '18 at 12:59
  • Create a SQL Login instead and assign it as owner. – Alberto Morillo May 18 '18 at 13:59
  • Well, the user sa still couldn't be created, but as it is no longer the owner, I went ahead with the migration, then uploaded my updated code and connected it to the database ok and my site works! Thank you Alberto. – Alistair67 May 18 '18 at 14:35
  • Thank you for letting me know. – Alberto Morillo May 18 '18 at 16:05
  • Adding a SQL Server Active Directory Admin (temp), and logging in with that account also helped. – demokritos Dec 17 '18 at 17:22