This article guides to resolved,
How to resolve Cannot export bacpac in SQL Server Error SQL71564? | The element has been orphaned from its login and cannot be deployed
What is bacpac?
Bacpac is backup file imported from AZURE SQL database or SQL Server. Bacpac is used to to migrate databases from SQL Server to AZURE SQL Database. You can export bacpac file from SQL Server management studio or using SQLPackage.exe module using PowerShell script.
If you want to take bacpac check below link.
How to create bacpac file from SQL server? | export bacpac file sql server | How to create a BACPAC file from SQL Server database?
Sometime while importing bacpac we get below error
sqlpackage.exe : *** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
At line:1 char:1
+ & 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe' ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (*** Error expor...a data package.:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Error SQL71564: Error validating element [test]: The element [test] has been orphaned from its login and cannot be deployed.
Error SQL71564: Error validating element [test1]: The element [test1] has been orphaned from its login and cannot be deployed.
Error SQL71564: Error validating element [Test11]: The element [Test11] has been orphaned from its login and cannot be deployed.
Error SQL71564: Error validating element [Test2]: The element [Test2] has been orphaned from its login and cannot be deployed.This error occurred due to some of the orphan users present in database on SQL Server.
Orphan users are users which respective logins are not present on SQL Server or users not mapped with that logins.
Bacpac generation process doesn’t support orphan users and it will give below error while taking bacpac.
Error SQL71564: Error validating element [test]: The element [test] has been orphaned from its login and cannot be deployed.
How to resolve Cannot export bacpac in SQL Server Error SQL71564?
We need to resolve Orphan users before generating bacpac on each database on SQL Server. We can either delete those users from database or map permission to the user from respective login.
Use below sample script to map user to login.
USE [AdventureWorks2014]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2014]
GO
ALTER ROLE [db_owner] ADD MEMBER [test]
GO
USE [AdventureWorks2016]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2016]
GO
ALTER ROLE [db_owner] ADD MEMBER [test]
GO
USE [AdventureWorks2019_new]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [AdventureWorks2019_new]
GO
ALTER ROLE [db_owner] ADD MEMBER [test]
GO
--To drop User
USE [StackOverflow2013]
GO
DROP USER [Test11]
GO
Once you drop all those users you will able to take bacpac successfully. If you want to know
You will get below error sometime while taking bacpac
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element [dbo].[DatabaseBackup]: Procedure: [dbo].[DatabaseBackup] has an unresolved reference to object [master].[dbo].[xp_instance_regread]. External references are not supported when creating a package from this platform.
This is due to procedure which are using external reference like calling procedure from another procedure or linked server which does not support in Azure SQL Database. You need to remove that reference or modify procedure as per guideline of Azure SQL database and try to take bacpac.
How to take bacpac using SQL package.exe watch
1 thought on “How to resolve Cannot export bacpac in SQL Server Error SQL71564? | The element has been orphaned from its login and cannot be deployed”