3

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?

user1054922
  • 2,101
  • 2
  • 23
  • 37
  • 2
    This has nothing to do with the "proper way". The error is literally telling you the problem. `fn_Table_Predicate` references `Table` with `SCHEMA_BINDING` so you cannot `ALTER` `Table`. You'll need to `DROP` any objects that reference `Table`, perform the `ALTER` and then recreate them. – Thom A Dec 07 '19 at 22:08
  • 1
    Wow, that's a big rabbit hole to go down. Perhaps just disabling SCHEMA_BINDING on the predicates is the way to go. – user1054922 Dec 08 '19 at 16:46

1 Answers1

5

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.

keikai
  • 14,085
  • 9
  • 49
  • 68
Matias Llapur
  • 66
  • 1
  • 3