How to Configure Azure SQL Read Only User

Published
Length
3 min read

Luis Felipe Dias

All posts

To create an azure sql read only user, map a login or contained user to the built-in data reader role. This setup lets people read all user tables and views without changing any data. Many teams start by sharing one broad admin account during early project work. Overly broad roles let one bad query update or drop live production tables. Moving to a strict read role stops accidental edits right away. How an azure sql read only user works A database user is the identity of a login inside a targ

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.

To create an azure sql read only user, map a login or contained user to the built-in data reader role. This setup lets people read all user tables and views without changing any data. Many teams start by sharing one broad admin account during early project work. Overly broad roles let one bad query update or drop live production tables. Moving to a strict read role stops accidental edits right away.

How an azure sql read only user works

A database user is the identity of a login inside a target database. Teams pick between two main identity models based on how many databases a person needs. If someone needs access to many databases on an instance, use a standard login. You create the login in the master database first. Then you create a database user tied to that login inside each database.

If someone only needs one or a few databases, use a contained database user. A contained user holds its own password right inside the target database. It does not tie back to a login in the master database. An admin must first turn on contained databases on the server and the database. Microsoft supports thirteen total types of database users across its platform.

Once the user exists, you grant read rights through a fixed database role. Every database role, including the default public role, already includes basic connect rights. Adding the user to the data reader role grants read access across the database. Members of this role can read all data from all user tables and views. You cannot change the base permissions assigned to these fixed database roles.

How fixed roles handle write blocks and overrides

Database admins also use deny roles to set hard boundaries on accounts. The deny data writer role stops a user from adding, changing, or deleting data. This acts as a safety net if a user belongs to several groups. The deny data reader role blocks all reads from user tables and views. It also blocks the user from reading metadata about those tables and views.

Teams must avoid assigning the owner role for routine support or reporting tasks. The owner role allows all setup and maintenance actions inside the database. A user in that role can even drop the whole database. Standard read roles keep day-to-day queries safe from accidental write commands.

Read-Only Root Filesystem + Azure RBAC: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native read roles solve and what they leave open

Native database roles do a great job of stopping unwanted write queries. When an engineer runs an update or delete statement, the database blocks it. The fixed data reader role keeps your tables safe from accidental edits.

Still, native setup only decides who logs in and which tables they can read. It does not check what individual select statements return while queries run in flight. A read role grants full visibility into every column of every user table by default. If a table holds emails, phone numbers, card numbers, or national IDs, queries return them.

Static database accounts also create credential management work for the security team. People store database passwords on their laptops or share them in team chats. When someone changes roles, revoking access means hunting down shared passwords across many apps. You also lack a clear audit trail when five engineers share one read login. The database log shows the shared login name, not the person at the keyboard.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server, inline in the TDS wire protocol. On every batch an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Before a batch hits the server, hoop.dev guardrails refuse disallowed actions with a native TDS error or hold risky statements for approval. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns in memory, and every decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

When should I use a contained user instead of a login in master?

Use a contained user when someone needs access to just one or a few databases. Use a login in the master database when they need access to many databases.

Can I customize the permissions inside the fixed data reader role?

No, you cannot change the permissions assigned to built-in fixed database roles. The data reader role always grants read access to all user tables and views.

Does a read role hide sensitive columns like card numbers or emails?

No, the fixed data reader role lets users read all data in user tables. To hide emails or card numbers in results, you need column rules or data masking.

The query runs. The emails stay hidden.