SQL Compare shows syntax error while SQL Server accepts syntax (SCHEMABINDING)

JamesW

I'm able to run the following script against a 2017 version of SQL Server.

<div>GO<br></div><div><div>CREATE SCHEMA [security];</div><div>GO</div><div>CREATE FUNCTION [security].[tvf_AllowEditNonAvailableCardsPredicate]</div><div>(</div><div>&nbsp; &nbsp; @LastName VARCHAR(100),</div><div>&nbsp; &nbsp; @LegalName VARCHAR(65),</div><div>&nbsp; &nbsp; @NameIndex VARCHAR(1000)</div><div>)</div><div>RETURNS TABLE</div><div>WITH SCHEMABINDING</div><div>AS</div><div>RETURN SELECT 1 AS Allow</div><div>&nbsp; &nbsp; &nbsp; &nbsp;WHERE @LastName <> 'Available'</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND @LegalName <> 'Available'</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND @NameIndex <> 'Available';</div><div>GO</div><div>CREATE SECURITY POLICY [security].[AllowEditCard]</div><div>ADD BLOCK PREDICATE [security].[tvf_AllowEditNonAvailableCardsPredicate] (LastName, LegalName, NameIndex)</div><div>ON dbo.Cards BEFORE UPDATE</div><div>WITH</div><div>(STATE = ON, SCHEMABINDING = ON);</div><div>GO</div></div>

SQL Source Control doesn't save the SCHEMABINDING = ON for the security policy, and when I tried to add it manually to source control and then compare against local, it says that it's a syntax error and ignores it. However, SQL accepts it and the option is valid according to microsoft docs. (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-2017)

Am I doing something wrong or is this a bug that needs to be fixed?

Alex B

Hi @JamesW ,

I've deleted the duplicate of this post so you may have received a message indicating that.

For the question here, I see the same occurring and have raise the issue with the development team as SC-10673.  I will update here when I have more information from the team!

Kind regards,
Alex

JamesW

Thanks! I'll work around it for now. (Not sure if I was supposed to accept the answer or not )