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

Question

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!

try this `ALTER SECURITY POLICY yourpolicy DROP filter/block PREDICATE ON table` — TheGameiswar, Aug 17 '17 at 10:34
Perfect thank you... as a note I had to include the AFTER UPDATE for the Block predicate... otherwise it says the predicate doesn't exist. Add as an answer & I'll mark correct — ThomasRones, Aug 17 '17 at 10:54
you can post that as an answer ,with what you had and what were you trying and what you did,it may help others in future — TheGameiswar, Aug 17 '17 at 10:55

score 1 · Accepted Answer · answered Aug 17 '17 at 11:01

1

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:

answered Aug 17 '17 at 11:01

ThomasRones

657
8
29

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

1 Answers1