2

I have the following rather simple statement in SQL Server Mgmt Studio 2016 CTP 3.2:

if exists(select null from sys.security_policies where name = 'OrganisationSecurityPolicy')
begin
    alter security policy [security].OrganisationSecurityPolicy
    with (state = off)
end

A security policy with that name does indeed not exist so nothing should happen. However, I get an error:

Msg 33268, Level 16, State 2, Line 3
Cannot find the object "security.OrganisationSecurityPolicy" because it does not exist or you do not have permissions.

Is there something special about security policies that makes this impossible? If I replace the alter security policy with an alter table for a non-existing table, the statement simply isn't executed.

  • Perhaps you have the incorrect schema in the alter security policy... statement? Note you are not checking the schema name in the if exists(...) statement. – Max Vernon Dec 30 '15 at 15:20
  • The security policy exists in the security schema. The name in sys.security_policies is without the schema. So everything is correct there. – Ronald Wildenberg Dec 31 '15 at 6:22
1

Statements go through three processing steps - parsing, binding and execution, in that order. This batch is failing at binding since it cannot find an object to match the typed words. The work-around is to use dynamic SQL.

See a related question here.

  • I'm now using dynamic SQL and this works (of course). It seems a bit random to me which statements fail during parsing and which in a later stage. An alter table for a non-existing table instead of alter security policy doesn't have this problem. I don't see the logic there... – Ronald Wildenberg Jan 4 '16 at 15:55
  • I would have thought it was the difference between DML (optimised and hence bound) and DDL (not optimised or bound). But this states ALTER SECURITY POLICY is DDL. Go figure. – Michael Green Jan 5 '16 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.