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
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
Ok I have no idea where to go from here....
There must be an easier way! I just want to alter a function!
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: