Import bacpac containing security policies and temporal-tables with schemabinding
1
vote
0
answers
744
views
I've created a
bacpac
backup of my database (both Azure and local with SSMS). Trying to import the bacpac
to same or another machine (again both Azure and local) the import always fails with this error:
>Error SQL72014: .Net SqlClient Data Provider: Meldung 3729, Ebene 16, Status 4, Zeile 1 Cannot ALTER 'SecTable' because it is being referenced by object 'sec_SecTable'. Error SQL72045: Fehler bei der Skriptausführung. Ausgeführtes Skript: ALTER TABLE [dbo].[SecTable] SET (SYSTEM_VERSIONING = OFF); ...
SQL-Server versions: 14.0.3223.3 and 15.0.1800.32.
Already tried different versions of SSMS (17,18) and via SqlPackage.exe
(Visual Studio 19 and SSMS 17/18) to execute the import/export of the bacpac
.
The smallest DB creation script that will reproduce this problem is:
CREATE TABLE [dbo].[SecTable](
[Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
[SystemHistoryStart] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[SystemHistoryEnd] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
[SecValue] [int] NOT NULL
CONSTRAINT [PK_SecTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([SystemHistoryStart], [SystemHistoryEnd])
) ON [PRIMARY]
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[SecTable_History]))
GO
-- Function
CREATE FUNCTION [dbo].[sf_SchemaBoundTableFunction]
(
@secValue int
)
RETURNS TABLE
WITH SCHEMABINDING
RETURN
SELECT 1 AS Result
GO
-- security policy
CREATE SECURITY POLICY [dbo].[sec_SecTable]
ADD BLOCK PREDICATE [dbo].[sf_SchemaBoundTableFunction]([SecValue]) ON [dbo].[SecTable] BEFORE DELETE
WITH (STATE = ON, SCHEMABINDING = ON)
GO
Any suggestions or workaround how to solve this problem or how to configure the import/export of the bacpac
without removing the schemabinding
? Is it maybe a bug or am I doing something wrong?
Asked by AKL
(11 rep)
Sep 16, 2019, 03:22 PM
Last activity: Sep 16, 2019, 05:12 PM
Last activity: Sep 16, 2019, 05:12 PM