Altering MSSQL table protected by RLS (SSDT)

Question

When adding a new column to a table protected by Row Level Security in MSSQL 2016, I get the following error when trying to publish the database using SQL Server Data Tools:

The current operation will cause data motion on table X. Data motion can not be performed on this table because it has row level security enabled by policy Y. To allow this operation use SqlPackage.exe with option /p:AllowUnsafeRowLevelSecurityDataMovement

I suspect that it wants to recreate the table, which might result in data loss if the deployment user is not granted access to data by the RLS predicates. However I am unable to find any documentation about this.

More importantly, what is best practice for handling such deployment scenario ?

Answer 1

There are 2 aspects to this scenario:

• Why is the addition of a column causing a data motion operation to occur?
• What is the danger introduced by Row Level Security?

Minimizing / avoiding data motion

In general adding a new column should not require data motion. If added to the end of a table definition this can be done via an ALTER TABLE ADD COLUMN operation, and SSDT is smart enough to do this for you. However if added in the middle of a table the default behavior is to do a full data motion which can be very costly on large tables. To avoid this:

• Always try to add columns to the end where possible.
• If this is not possible, recent versions of SSDT & SqlPackage have an IgnoreColumnOrder flag that will treat column additions in the middle of the table as if they were added to the end. This can cause some complications (e.g. schema compare must have this set too or it'll show differences between the source project & database version of the table), but is usually worth it to avoid unnecessary data motion during deployment

Handling Row Level Security (RLS) + Data Motion

If you do really need to make a change that causes data motion, the risk with RLS is that your user doesn't have access to all rows in a table. Since data motion copies data to a temp table, then drops the original & does a rename, you would lose any rows that your user could not see.

The solution is to set the /p:AllowUnsafeRowLevelSecurityDataMovement flag, but to verify that your user is not part of any row level filtering scheme. A good practice is to only apply those schemes to users with limited permissions (e.g your app roles), while using a role with greater permissions when deploying (an admin role). If you do this you're safe to deploy, but the flag is there to at least warn about this / avoid accidental data loss.