How to Configure Codex SQL Server Read Only Access
To set up codex sql server read only access, create a dedicated database user. Then add that user to the fixed reader role and block write roles. Engineering teams often connect AI coding agents to databases using personal developer accounts. Those accounts usually hold broad owner rights across the instance. An AI agent with owner rights can alter tables or drop the entire database. Setting up codex sql server read only access with native roles SQL Server separates server logins from databas
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 read only access, create a dedicated database user. Then add that user to the fixed reader role and block write roles. Engineering teams often connect AI coding agents to databases using personal developer accounts. Those accounts usually hold broad owner rights across the instance. An AI agent with owner rights can alter tables or drop the entire database.
Setting up codex sql server read only access with native roles
SQL Server separates server logins from database users. A login lives in the master database and lets an identity reach the server. A database user maps that login to one specific database. If the agent needs several databases on one server, create a master login first. Then create a database user inside each target database for that login.
You can also use a contained database user for a single database. A contained user holds its own password inside the target database. It does not need a login in the master database. An admin must first turn on contained databases on the server and database.
Once the user exists, you grant read rights through fixed database roles. Adding the user to the fixed data reader role allows reading all user tables. It also allows reading all views in that database. You cannot change the permissions built into a fixed database role. Every database user also belongs to the public role by default. The public role includes basic permission to connect to the database.
To lock down the account, add the user to the deny data writer role. Members of this role cannot add, change, or delete table data. Never put an AI agent in the database owner role. The owner role allows all maintenance tasks and can drop the database.
How the agent runs queries and where native roles fall short
Once you create the read-only user, you give those credentials to the agent. AI coding agents reach databases through local command tools or model context servers. The agent reads the database credentials from local files or workspace variables. It opens a direct session to SQL Server and sends queries on its own.
Native database roles solve the problem of who can log in. They also stop the agent from running write commands or dropping tables. Still, native roles do not inspect what individual queries do in flight. A member of the fixed data reader role can read every user table.
Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
That broad read scope creates two major risks for engineering teams. First, the agent can read sensitive columns in production tables. Query results can pull customer emails, phone numbers, card numbers, or national IDs. That raw data then flows straight into the model prompt context. Second, a read-only user can still run huge joins or full table scans. One bad query from an agent can lock resources and slow down production.
Reducing exposure on tables and local machines
You can narrow native access by skipping the fixed data reader role. Instead, grant select rights only on specific views or safe tables. You can also add the user to the deny data reader role on sensitive databases. Members of that role cannot read any user tables, views, or table metadata.
Even with strict table grants, static credentials remain a risk on developer laptops. When you store passwords in local text files, any local process can read them. Rotating those passwords across a whole team takes manual effort and breaks local setups. Teams need a way to inspect queries and mask fields like card numbers in results.
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
Can I edit the permissions of the fixed data reader role in SQL Server?
No, SQL Server does not let you change permissions on fixed database roles. If the fixed data reader role is too broad, create a custom role and grant select rights on specific views.
Should I use a master login or a contained user for an AI agent?
Use a contained database user when the agent only needs one or a few databases. Use a login in the master database when the agent must read across many databases on the same server.
Does a read-only SQL Server role stop an AI agent from reading sensitive columns?
No, the fixed data reader role grants read access to all user tables and views. Without extra controls or data masking, queries will return raw emails, phone numbers, and card numbers.
Related guides
In this post