Dynamic Data Masking vs. Row-Level Security: What Each Actually Protects

Both features shipped in the same SQL Server version (2016), both get described as "row-level" or "column-level" security in casual conversation, and both get reached for in the same kind of meeting: "the support team needs to see customer records, but not the credit card numbers" or "sales reps should only see their own region's data." They solve two completely different problems, and using one where you needed the other doesn't fail loudly, it just quietly doesn't protect what you thought it did.

Two Different Shapes of Problem

	Dynamic Data Masking (DDM)	Row-Level Security (RLS)
What it hides	Values within specific columns	Entire rows
Typical question it answers	"Can this user see the real credit card number, or a masked one?"	"Can this user see this row at all?"
Underlying data	Stored in plain text; masking happens only in the query result	Stored normally; filtering happens transparently at query time via a predicate
Bypassed by	Anyone with `UNMASK` permission, or in some versions, direct access outside the querying application context	Nothing, if configured correctly, it applies regardless of which tool or query issues the request
Security guarantee	Weak; explicitly documented by Microsoft as not a substitute for encryption or proper access control	Strong when combined with proper permission design; enforced consistently at the engine level

Dynamic Data Masking: A Presentation Layer, Not a Security Boundary

ALTER TABLE dbo.Customers
ALTER COLUMN CreditCardNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Grant a user permission to see real, unmasked values
GRANT UNMASK ON dbo.Customers TO SupportLead;

Query the table as a user without UNMASK, and CreditCardNumber comes back as XXXX-XXXX-XXXX-1234 instead of the real value. The actual data underneath is completely unchanged, unencrypted, unfiltered, DDM only intercepts what gets displayed in the result set.

Masking is cosmetic, and Microsoft says so directly. Anyone with sufficient permission to run ad-hoc queries can often work around masking, historically through inference attacks (e.g. brute-forcing values via WHERE clause comparisons) even without UNMASK. Treat DDM as a UI convenience that keeps values off a screen for reporting-tool users with limited technical means, not a security control keeping a determined party away from data they otherwise have query access to. It pairs with, and never substitutes for, the real access controls covered in GRANT, DENY, REVOKE and encryption at rest via Transparent Data Encryption.

Row-Level Security: An Enforced Predicate, Not a View

CREATE FUNCTION dbo.fn_SalesRegionFilter(@Region AS sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Result
    WHERE @Region = USER_NAME() OR IS_MEMBER('SalesManagers') = 1;
GO

CREATE SECURITY POLICY dbo.SalesRegionPolicy
    ADD FILTER PREDICATE dbo.fn_SalesRegionFilter(RegionOwner)
    ON dbo.SalesOrders
    WITH (STATE = ON);

Once this policy is active, every query against dbo.SalesOrders, from any tool, any login, any report, silently only returns rows the predicate allows. There's no separate "masked" mode to bypass; the filter applies at the engine level regardless of how the query arrives. A sales rep querying directly in SSMS gets exactly the same row restriction as one going through the application's reporting layer.

Predicate type	Effect
Filter predicate	Silently excludes rows from `SELECT`, `UPDATE`, and `DELETE`, as if they don't exist
Block predicate	Explicitly blocks `INSERT`/`UPDATE`/`DELETE` operations that would violate the predicate, raising an error instead of silent exclusion

Common Gotchas

Assuming DDM protects against a malicious insider with query access. It doesn't, and isn't designed to; it protects against accidental over-exposure to users who were only ever supposed to see masked values through an approved reporting path.
RLS predicate functions that are expensive per row. Because the predicate runs against every row a query touches, a poorly written or unindexed predicate function turns into a performance problem across every query on that table, not just the ones that appear to need filtering.
Forgetting RLS applies to UPDATE/DELETE too, not just SELECT. A filter predicate without a matching block predicate can let a user update or delete a row that then "disappears" from their own subsequent view, confusing behavior if the block predicate wasn't deliberately added.
Combining both and assuming double protection where there's a gap. DDM and RLS solve different axes (column values vs. row visibility) and can absolutely be layered on the same table, but neither one covers what the other doesn't, verify both independently rather than assuming coverage compounds automatically.
db_owner and similarly privileged roles typically bypass RLS predicates. Row-level security is meant to constrain application-level users, not administrators; don't rely on it as a boundary against roles that already have broad table access, that's a job for the permission model covered in SQL Server Security: Logins, Users, Roles & Permissions.

The Bottom Line

Ask which axis actually needs protecting: specific column values from users who otherwise have legitimate table access (masking), or entire rows from users who shouldn't see them regardless of which column they'd look at (RLS). They're not interchangeable, and neither is a substitute for solid permission design or encryption at rest, they're both refinements layered on top of an access control model that has to already be correct underneath.

← Back to Blog