I am trying to import a .bacpac file to my local SQL Server using both SSMS and sqlpackage. I get the same error when trying this via a script using SQLPackage.exe or doing this manually from SSMS:
This is the script I am running:
.\SqlPackage.exe /Action:Import /SourceFile:"C:\Backup\mydb.bacpac" /TargetConnectionString:"Data Source=LAPTOP-DELL-DAVE\SQLEXPRESS;Initial Catalog=copyX; Integrated Security=true;Encrypt=False"
Here is the output of running the above script:
Creating deployment plan
Initializing deployment
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
*** Error importing database:Could not import package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. Alternatively, use the server level 'ALTER ANY CONNECTION' permission.
Error SQL72045: Script execution error. The executed script:
GRANT KILL DATABASE CONNECTION TO [3d72312312312wo];
Please do not suggest using this script as I tried it already:
sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
I have also tried to import on both SQL Server 15 and the latest version SQL Server 16 and both returns the same error message.
I found out that 3d72312312312wo
is an orphaned user. I had a username similar to that which is why it took me a long time to notice it. But I had backed up this database from Heroku MSSQL to Microsoft Azure and this userID must have came in with it and I just never realized. Removing that userID from the database fixed the issue.