How to Configure Codex SQL Server Approval

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up codex sql server approval, pair a strict database role with agent prompts. This setup stops AI coding agents from running bad changes on live tables. Many teams start by giving an AI agent a broad database login. That shortcut lets the agent run anything that the user account allows. When a role is too wide, one bad prompt can delete rows or expose emails. How native codex sql server approval and roles work SQL Server manages database access through server logins and database users.

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 codex sql server approval, pair a strict database role with agent prompts. This setup stops AI coding agents from running bad changes on live tables. Many teams start by giving an AI agent a broad database login. That shortcut lets the agent run anything that the user account allows. When a role is too wide, one bad prompt can delete rows or expose emails.

How native codex sql server approval and roles work

SQL Server manages database access through server logins and database users. A database user is the identity of a login inside one specific database. When someone needs to reach many databases on an instance, teams create a login. That login lives in the master database and maps to users in other databases. For one or a few databases, teams can also use a contained database user. 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 the database. SQL Server supports thirteen different types of database users for different needs.

Once the user exists, admins assign it to fixed database roles. Every role, including the default public role, starts with the right to connect. The fixed read role lets members read all data from all user tables. That same role also lets the user read all views in the database. The fixed write role lets members add, delete, or change data in user tables. Admins can also assign deny roles to block reading or writing across user tables. The owner role can run all maintenance tasks and can even drop the database. SQL Server does not let you change the rights inside fixed database roles.

Why native roles and local prompts leave a gap

Giving an AI coding agent the fixed read role blocks table changes. You can also add the deny write role so the user cannot change rows. On the client side, AI coding agents can ask a human before running commands. Local agent rules can pause shell tools and wait for a person to click approve. This two-part setup helps stop simple mistakes during daily coding tasks.

Still, static database roles only decide who logs in and which tables they touch. They cannot check what an individual SQL query does while it runs. A user in the fixed read role can still read every user table. If a table holds phone numbers, card numbers, or national IDs, the query gets them. The database sends all matching rows back to the AI agent context window. Even worse, if the task needs write rights, the fixed write role opens all tables. A human checking a prompt on a laptop might miss a bad join or filter. Local client rules also sit on the user machine, where settings can drift across teams.

Approval Chains & Escalation + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What to check before approving agent database queries

Teams that let AI coding agents query SQL Server need clear review rules. First, never connect an agent with the database owner role. That role can drop the database or change security settings with one statement. Second, split read tasks from write tasks by using separate database users. Keep the daily agent user on the fixed read role and the deny write role.

Third, review every write statement for missing filter clauses before it hits the server. A human reviewer should see the exact SQL text and the target database name. Fourth, plan for sensitive columns like emails and card numbers in user tables. Because the fixed read role reads all user tables, native roles alone cannot hide columns. Rewriting matched fields in query results keeps raw personal data out of agent logs. Centralizing these checks outside the laptop ensures every team member follows the same rule.

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

Can I edit the permissions of the built-in SQL Server read role?

No, SQL Server does not let you change the rights of fixed database roles. If the built-in read role is too broad, you must create a custom role. You can also combine fixed roles with deny roles to block specific actions.

Should an AI coding agent use a master login or a contained user?

Use a contained database user when the agent only needs one database. It stays inside that database and does not need a login in master. An admin must enable contained databases on the server and database first.

Does the built-in read role hide sensitive columns from AI agents?

No, members of the fixed read role can read all data from all user tables. That includes columns with emails, phone numbers, card numbers, or national IDs. You need extra controls or result masking to rewrite those fields before the agent reads them.

The query runs. The emails stay hidden.