How to Configure Azure SQL Column Level Security

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up azure sql column level security by granting read rights on chosen columns. You can also deny read rights on sensitive columns for a database role. Many teams start by giving users a broad fixed role for all tables. This broad setup exposes sensitive fields like emails, phone numbers, and card numbers. Every person in that role can then read every column in every table by default. How azure sql column level security works First, an admin sets up an identity in the database for the p

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 azure sql column level security by granting read rights on chosen columns. You can also deny read rights on sensitive columns for a database role. Many teams start by giving users a broad fixed role for all tables. This broad setup exposes sensitive fields like emails, phone numbers, and card numbers. Every person in that role can then read every column in every table by default.

How azure sql column level security works

First, an admin sets up an identity in the database for the person. A database user is the identity of a login for that database. For people who need many databases, a login in master is common. For one or a few databases, a contained database user is an option. An admin must first turn on contained databases on the server and database. Across the platform, there are thirteen types of database users available to admins.

Next, the admin groups these database users into roles to manage their rights. Fixed roles like db_datareader let members read all data from all user tables. They also read all views, and fixed role permissions cannot be changed. To limit columns, you must avoid giving users the broad db_datareader role. Instead, you create a custom role and grant read rights on safe columns. You can also put a deny rule on columns that hold national IDs. When a user runs a query, the engine checks the requested columns first. If the query touches a restricted column, the database blocks the whole query.

What native column rules solve and what they miss

Native column rules stop unauthorized users from reading restricted fields through standard queries. They help teams separate public table data from private fields like card numbers. You do not need to duplicate tables just to hide a few columns. You can also build views that show only the allowed columns to readers. This keeps the storage layout clean while limiting what basic accounts can read.

Still, static database grants only decide who logs in and which columns fail. They do not inspect or adapt to what individual queries do in flight. If a query selects all columns, the database returns an error and fails. This breaks apps and dashboards that expect a full table structure back. Maintaining column grants across hundreds of tables also takes a lot of work. Every schema change requires an admin to check and update role rules by hand. Most of all, admins and support engineers often use high-level roles during fixes. For instance, the db_owner role can run all maintenance tasks and drop databases. When engineers use broad roles to fix issues, native column blocks do not apply. Their queries return raw emails, phone numbers, and national IDs in clear text.

Column-Level Encryption + Azure RBAC: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why teams pair database roles with runtime controls

Relying only on static role grants forces a hard choice during daily work. You either block whole queries or give engineers broad read rights across tables. When you grant broad rights for debugging, sensitive columns flow back to clients. A better plan keeps queries working while rewriting sensitive fields in the results. That way, an engineer can run a broad query without breaking the tool. At the same time, masking rewrites matched fields like card numbers in results.

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

Can I change the permissions of the db_datareader role to hide one column?

No, the permissions assigned to fixed database roles cannot be changed by admins. Still, a deny rule on a column overrides the read rights of db_datareader. You can also create a custom role and grant only the safe columns.

What happens when a user queries a table with a denied column?

If the user includes the restricted column in the query, the query fails. The database engine blocks the entire statement and returns a permission error message. The user must rewrite the query to ask for only the allowed columns.

Should I use a login in master or a contained database user?

Use a login in master when a person needs access to many databases. Use a contained database user when a person needs only one database. Remember that an admin must enable contained databases on the server and database.

The query runs. The emails stay hidden.