How to Configure Azure SQL User Roles
You set up azure sql user roles by creating a database user and assigning a role. You can map a server login or make a contained user for one database. Many teams start by giving developers broad owner roles to move fast. Broad roles let people change tables, read private data, or drop the whole database. How azure sql user roles and logins work A database user is the identity of a login inside a single database. Microsoft supports 13 types of database users across the engine. People who use
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 user roles by creating a database user and assigning a role. You can map a server login or make a contained user for one database. Many teams start by giving developers broad owner roles to move fast. Broad roles let people change tables, read private data, or drop the whole database.
How azure sql user roles and logins work
A database user is the identity of a login inside a single database. Microsoft supports 13 types of database users across the engine. People who use many databases on one server often use a standard login. That login lives in the master database and maps to each target database.
Teams with one or a few databases can use a contained database user instead. A contained user has its own password and does not need a master login. An admin must first turn on contained databases on the server and database.
Once the user exists, an admin places that user into a database role. Every database user starts in the public role, which grants basic connect rights. Fixed roles group common read, write, and admin rights together for easy setup. You cannot change the rights built into these fixed database roles.
Choosing between fixed read and write roles
The data reader role lets members read all data from all user tables and views. The data writer role lets members add, delete, or change data in all user tables. Some teams combine both roles when an engineer needs to fix records by hand.
Database admins also use deny roles to block data rights for specific people. The deny data reader role stops a user from reading any user tables or views. It also blocks that user from reading metadata about those tables and views. The deny data writer role stops a user from adding, changing, or deleting table data.
The owner role sits at the top of the database role list. Members of the owner role can run all setup and maintenance tasks. They can even drop the database in SQL Server. Giving out the owner role for daily work creates huge risk for production data.
Azure RBAC + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they leave open
Native roles do a good job of sorting who can log in and read tables. They separate full admins from people who only need to run select queries. This setup stops a read-only user from dropping a table or changing rows.
Still, database roles only check who logs in and which tables they can touch. A data reader role lets a user read every column in every user table by default. If a table holds emails, phone numbers, or card numbers, the reader gets them all. Native fixed roles do not rewrite sensitive fields in query results on the fly.
Roles also stay active all the time once an admin sets them up. An engineer with the data writer role can run an update statement any day. The role itself does not require a peer review before a risky query runs. Teams need extra controls to check individual statements and mask fields like national IDs.
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
What is the difference between a login and a database user?
A login lives in the master database and works across many databases on an instance. A database user is the identity inside one database, and contained users skip the master login.
Can I edit the rights inside a fixed database role?
No, Microsoft locks the rights assigned to fixed database roles. If you need custom rules, you must make a custom role and grant specific rights.
What happens if a user is in both data reader and deny data reader?
The deny role wins and blocks the user from reading any user tables or views. It also stops the user from reading metadata about those tables and views.
Related guides
In this post