CODEWITHSUNDEEP
sql-serverrow-level-securitytemporal-tables
user1054922
user1054922

Reputation: 2175

Cannot ALTER 'Table' because it is being referenced by object 'predicate' - SQL Server RLS with Temporal

I'm using Row-Level Security on a temporal table in my SQL Server database. In order to comply with GDPR, I need to be able to not only delete this data in this table that is from the present, but also its _History table (temporal table).

Before RLS, I used to do:

ALTER TABLE [dbo].[Table] SET (SYSTEM_VERSIONING = OFF);
DELETE FROM [dbo].[Table_History] WHERE UserID=@userID;
ALTER TABLE [dbo].[Table] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Table_History]));

But now, this generates the error:

Cannot ALTER 'Table' because it is being referenced by object 'fn_Table_Predicate'.

What's the proper way to enable & disable system versioning with RLS enabled?

Upvotes: 5

Views: 7505

Answers (1)

Matias Llapur
Matias Llapur

Reputation: 96

My situation

I had a similar problem when altering a view which was referencing another view. I've renamed the views for the sake of the example:

When trying to alter my view AAAAA I got this message:

Cannot ALTER 'AAAAA' because it is being referenced by object 'BBBBB'.

My view BBBBB was created using "WITH SCHEMABINDING"

How I solve it

So I've altered my view BBBBB just removing "WITH SCHEMABINDING" (the remaining logic was kept the same).
Then I've altered my view AAAAA.
When done, I've altered back my view BBBBB adding again "WITH SCHEMABINDING"

Hope this helps.

Upvotes: 8

Related Questions