How to Configure SQL Server Row Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up sql server row level security by linking a filter function to a policy. The database then checks every query and hides rows that do not match the user. Teams often start by giving users broad database roles for daily work. A fixed reader role lets a user read all rows in every user table by default. That broad access creates risk when tables hold data for many customers or teams. If a user runs a wide query, they get every record in that table. Row filters help teams restrict which r

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up sql server row level security by linking a filter function to a policy. The database then checks every query and hides rows that do not match the user.

Teams often start by giving users broad database roles for daily work. A fixed reader role lets a user read all rows in every user table by default. That broad access creates risk when tables hold data for many customers or teams. If a user runs a wide query, they get every record in that table. Row filters help teams restrict which rows each person can read or change.

How sql server row level security works in practice

Setting up sql server row level security starts with clear user identities inside the database. A database user is the identity of a login when it connects to a database. SQL Server supports thirteen types of database users for different needs. If a person needs many databases on one instance, admins create a login in master. Then they map a database user to that central login. If a person needs one or a few databases, admins can use a contained user. A contained user has a password and does not link to a master login. An admin must first turn on contained databases on the server and the database.

Once users exist, the database engine uses predicates and policies to filter table rows. You write a function that checks if a row matches the current user or role. Then you bind that function to a target table using a security policy. Filter predicates hide rows during read queries, updates, and deletes. Block predicates stop users from inserting or changing rows that break your rule. The database runs this check inside the engine on every query. You do not need to rewrite app queries or build custom views for each team.

How fixed roles interact with row rules

Admins often mix row rules with built-in fixed database roles. You cannot change the permissions assigned to fixed database roles. Every role, including the public role, has the right to connect. Members of the fixed reader role can read all data from all user tables. Members of the fixed writer role can add, delete, or change data in all tables. A deny reader role stops a user from reading any table data or metadata. A deny writer role stops a user from adding, changing, or deleting any data. The owner role can do all maintenance tasks and can even drop the database.

When you add a user to the reader role, they can query any user table. Row rules then step in to filter which specific rows come back from that table. Still, shared logins and high roles still leave a major gap in your setup.

Row-Level Security + Server-Sent Events Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native row rules solve and what they leave open

Native row rules work well when every person and app has a unique database identity. The engine checks that identity and filters out rows from other tenants or regions. This keeps one customer or regional team from pulling another team's rows by mistake.

The gap appears during support work, debugging, and admin tasks. Developers and analysts often share a single service login or hold the owner role. When five engineers share one login, the database sees them as the same user. Row rules cannot tell those five people apart. Also, row filtering is all or nothing for a matched row. If a row passes the filter, the user gets every column in that row by default. Native row rules do not mask emails, phone numbers, card numbers, or national IDs. Finally, database roles decide who logs in, not what individual statements do in flight. They do not require peer approval before someone runs a risky delete or update statement.

Govern SQL Server queries and responses in flight

Table grants decide which SQL Server objects a caller may touch. They do not check query intent or rewrite sensitive values in results across every client and agent. By design, hoop.dev sits inline in the TDS wire protocol next to SQL Server as a sidecar for your SQL Server. On every production batch from an engineer or AI agent, hoop.dev decides to pass, mask, block, or hold it for approval.

With hoop.dev guardrails and hoop.dev data masking, banned statements are refused or held for review, matched sensitive fields in supported result columns are masked in memory, and each decision is logged. Check out the code on GitHub.

Frequently asked questions

What is the difference between a filter predicate and a block predicate?

A filter predicate hides non-matching rows when a user reads, updates, or deletes data. A block predicate stops a user from inserting or updating rows that break your rule.

When should I use a contained database user in SQL Server?

Use a contained user when someone only needs access to one or a few databases. An admin must first enable contained databases on both the server and the target database.

Can I change the permissions on SQL Server fixed database roles?

No, you cannot change the permissions assigned to fixed database roles like the reader role. You must use custom roles or security policies if you need granular table rules.

The query runs. The emails stay hidden.