How to Configure Pydantic AI SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up pydantic ai sql server read only access, create a database user first. Then assign that user only to a read role and block write roles. Many teams start by giving an AI agent a broad owner account to test queries. That shortcut lets a bad prompt change rows or even drop the whole database. Setting up pydantic ai sql server read only access with native roles A database user is the identity of a login inside a database. When an account needs many databases on one server, teams create

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 pydantic ai sql server read only access, create a database user first. Then assign that user only to a read role and block write roles. Many teams start by giving an AI agent a broad owner account to test queries. That shortcut lets a bad prompt change rows or even drop the whole database.

Setting up pydantic ai sql server read only access with native roles

A database user is the identity of a login inside a database. When an account needs many databases on one server, teams create a login in master. Then they map a database user to that login. If the agent only needs one database, a contained database user works well. This user has a password and does not tie to a login in master. An admin must enable contained databases on the server and the database first.

After you create the user, you must assign a role. Every database role includes basic connection rights by default. SQL Server includes fixed database roles with permissions that you cannot change. The standard read role lets members read all data from all user tables and views. You can also add the deny write role to the agent user. Members of the deny write role cannot add, change, or delete any table data. Never assign the owner role to an AI agent. Members of the owner role can run maintenance tasks and drop the database.

How Pydantic AI tools run SQL queries

In your Python app, the agent uses tool functions to talk to the database. You pass the database session into the agent through its dependency system. When a user asks a question, the language model picks the query tool. The tool runs the generated SQL string using the database user credentials. It then returns the rows to the model to build the final answer.

Because the model writes the SQL text, app code checks alone can fail. A prompt trick can make the model write a delete command instead of a select. If you enforce read rules inside SQL Server, the database engine blocks that write attempt. The agent gets an error message back, and your tables stay intact.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native SQL Server read roles fall short

Native roles decide who logs in and which tables they can read. They do not inspect what individual queries do while they run. A member of the fixed read role can read every user table and view. You cannot edit the permissions of that fixed role to hide specific columns. If a table holds emails, phone numbers, card numbers, or national IDs, the query reads them.

If the agent only needs a few tables, skip the fixed read role. Grant select rights only on the specific tables or views the agent needs. You can also add the deny read role to block all user tables and views. Even with custom grants, a single query can still pull thousands of sensitive rows. Native roles also do not tie a SQL command back to the prompt that triggered it.

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 runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.

With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. 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 fixed read role to hide one table from my agent?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. To limit table access, skip the fixed read role and grant select on specific tables instead.

What is the difference between a login user and a contained database user?

A standard SQL user maps to a server login stored in the master database. A contained database user logs in directly to one database after an admin enables that setting.

How can I be sure a read user never writes data?

Assign the user to the fixed deny write role Also, to your read grants. That role blocks the user from adding, changing, or deleting data in any user table.

The query runs. The emails stay hidden.