How to Configure Azure SQL Grant Permissions
To set up azure sql grant permissions, create a database user first. Then assign that user to a fixed role or give table rights. Teams often start by giving broad roles so engineers can work fast. Over time, these broad roles give too many people full database rights. When roles are too wide, one mistake can change or delete key data. How azure sql grant permissions work Database access starts by separating server logins from database users. A database user is the identity of a login inside o
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 set up azure sql grant permissions, create a database user first. Then assign that user to a fixed role or give table rights. Teams often start by giving broad roles so engineers can work fast. Over time, these broad roles give too many people full database rights. When roles are too wide, one mistake can change or delete key data.
How azure sql grant permissions work
Database access starts by separating server logins from database users. A database user is the identity of a login inside one database. If a person needs to reach many databases, admins create a server login. That login lives in the master database on the instance. Then the admin maps that login to a user in each target database.
If someone only needs one or a few databases, another option exists. Admins can create a contained database user with its own password. This user is not tied to a login in the master database. First, an admin must turn on contained databases on the server and database. Across the platform, there are thirteen types of database users available.
Once the user exists, admins assign rights using fixed database roles. Every role, including the public role, has basic rights to connect. You cannot change the built-in rights assigned to fixed database roles. To give read access, admins add the user to the datareader role. Members of this role can read all data from all user tables. They can also read data from all views in that database.
For write access, admins use the datawriter fixed role. Members can add, delete, or change data in all user tables. Admins can also block access using deny roles for reading or writing. The deny datareader role blocks reading from user tables, views, and metadata. The deny datawriter role stops users from adding, changing, or deleting data. At the top level, the owner role controls all setup and upkeep tasks. Members of the owner role can even drop the whole database.
What native roles solve and what they miss
Native database roles solve the problem of who can log in. They also set broad rules for who can read or write tables. Fixed roles are easy to set up for small teams with simple needs. Admins do not have to manage rights for every single table by hand.
Azure RBAC + AI Agent Permissions: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, fixed roles leave a large gap during daily work. The datareader role lets a user read every user table and view. It does not stop a query from pulling sensitive columns like emails. If a table holds phone numbers or card numbers, readers get them all. The datawriter role lets a user change or delete rows in any table. Native roles decide who gets in, not what each query does in flight.
Managing fine-grained rights across hundreds of tables takes a lot of manual work. Admins often fall back to the owner or datawriter roles to save time. Also, static database users keep their rights until an admin revokes them. If a password leaks, anyone with that key keeps full role access. Teams need a way to inspect queries 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 serves the whole server. A database user is the identity of that login inside one database. Contained users skip the master login and log in to one database.
What can members of the datareader and datawriter roles do?
Datareader members can read all data from all user tables and views. Datawriter members can add, change, or delete data in all user tables. You cannot change the built-in rights of these fixed roles.
How do you block a user from reading or writing data?
You can add the user to a deny role inside the database. The deny datareader role stops users from reading tables, views, or metadata. The deny datawriter role blocks users from adding, changing, or deleting data.
Related guides
In this post