sql-serveruser-defined-functionssql-server-2016security-policy

SQL Sever 2016 Alter Table Valued Function -- Alter Security Policy


I have a table-valued function which is being referenced by a security policy. I want to ALTER the function, but I cannot:

Msg 3729, Level 16, State 3, Procedure accessPredicate, Line 1
Cannot ALTER 'rls.accessPredicate' because it is being referenced by object 'EventSecurityPolicy'

Hmm but where is the security policy found in the GUI? I've checked the schema, table, and the function. I would assume that it would be under policies

enter image description here

Ok well I can always do it with T-SQL

select * 
from sys.security_policies

Maybe I can just turn it off

Alter security policy rls.EventSecurityPolicy
with (state = OFF);

Nope same error:

Msg 3729, Level 16, State 3, Procedure accessPredicate, Line 1
Cannot ALTER 'rls.accessPredicate' because it is being referenced by object 'EventSecurityPolicy'

OK let's delete it

delete from sys.objects
where schema_id = schema_id('rls') and object_id='1253579504'

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

How do you get past this sort of error: "Ad hoc updates to system catalogs are not allowed."?

OK I'll alter the assembly..

select * from sys.assemblies

enter image description here

Ok I have no idea where to go from here....

There must be an easier way! I just want to alter a function!


Solution

  • Solution --

    Drop the Predicates:

    ALTER SECURITY POLICY rls.EventSecurityPolicy DROP filter PREDICATE ON 
    dbo.Dim_event
    ALTER SECURITY POLICY rls.EventSecurityPolicy DROP BLOCK PREDICATE ON 
    dbo.Dim_event AFTER UPDATE
    

    Then modify the function:

    enter image description here