How to Configure Azure SQL PII

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up azure sql pii protection by creating scoped database users and roles. You then apply rules that restrict who can read sensitive tables and columns. Many teams start by giving developers a shared read-only role for daily work. This broad setup exposes emails, phone numbers, card numbers, and national IDs during routine checks. How native azure sql pii permissions work In SQL Server and Azure SQL, a database user represents an identity inside a database. Admins often create a login i

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 pii protection by creating scoped database users and roles. You then apply rules that restrict who can read sensitive tables and columns. Many teams start by giving developers a shared read-only role for daily work. This broad setup exposes emails, phone numbers, card numbers, and national IDs during routine checks.

How native azure sql pii permissions work

In SQL Server and Azure SQL, a database user represents an identity inside a database. Admins often create a login in the master database for people who use many databases. For one or a few databases, teams can use a contained database user instead. A contained user has its own password and does not tie to a master login. An admin must turn on contained databases on the server and database first. The platform supports 13 types of database users to match different identity setups.

Once the user exists, admins assign roles to control what data that user can read. The built-in public role and all other roles include basic permission to connect. Many teams add developers to the fixed db_datareader role so they can run queries. Still, members of db_datareader can read all data from all user tables and views. You cannot change the permissions assigned to fixed database roles.

If a table holds sensitive fields, db_datareader exposes every row and column to the user by default. Admins can use db_denydatareader to block all reads across user tables and views. They can also use db_denydatawriter to block users from adding, changing, or deleting data. Meanwhile, the db_owner role allows all maintenance tasks and can even drop the database.

Why static database roles fall short for sensitive fields

Native roles like db_datareader and db_datawriter work well for basic table access. They decide who can log in and whether an identity can read or write tables. They do not inspect what individual queries do in flight during a live session. When a developer needs to check an order table, they often need most columns. Blocking the whole table stops them from fixing bugs or checking app state.

To protect azure sql pii with native roles alone, admins must build custom views. They must maintain column rules for every single table that holds personal data. When schemas change, these custom rules can drift and leave new columns open. Shared accounts make this worse because audit logs only show the shared login name. Teams cannot tell which person ran a query that returned card numbers or emails.

Azure RBAC + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing query risks in live database sessions

Modern teams need a way to let engineers query production tables without exposing raw personal fields. Instead of managing hundreds of custom views, teams focus on controlling data in flight. Dynamic data masking rewrites matched fields like phone numbers or national IDs in query results. The engineer still gets the row count and non-sensitive columns needed to fix the issue.

Pairing field masking with per-user identity also improves audit logs across the whole team. Every query ties back to an individual person rather than a shared database login. Teams can also require quick peer reviews before someone runs a write query in production. This approach keeps daily database work fast while protecting sensitive customer records at the query level.

Add runtime guardrails beside SQL Server

Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev sits inline in the TDS protocol beside the database as a sidecar for your SQL Server. For every query an agent or human runs in production, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev SQL Server controls, rules refuse banned statements before they run, hold risky queries for human review, and mask matched sensitive values in supported result columns in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.

Frequently asked questions

Does the db_datareader role hide azure sql pii columns?

No, members of the db_datareader role can read all data from all user tables and views. You cannot edit the permissions of a fixed database role to hide specific columns. You must use custom roles or field masking to protect emails and card numbers.

When should I use a contained database user?

Use a contained database user when a person only needs access to one or a few databases. This user type authenticates at the database level without needing a login in master. An admin must first enable contained databases on the server and the target database.

What is the risk of using db_owner for app accounts?

Members of db_owner can run all maintenance tasks and can even drop the database. Giving an app or developer this role creates a high risk of accidental data loss. Always scope users to the minimum read or write permissions needed for their task.

The query runs. The emails stay hidden.