How to Configure Pydantic AI SQL Server Permissions
You set up pydantic ai sql server permissions by creating a dedicated database user. Then you assign that user to a read-only database role. Many engineering teams rush this step during early testing. They often give their AI agent a shared admin account to save time. This broad access creates a major risk for your production data. A single bad prompt can make the agent change or delete vital rows. It can even drop the whole database if it holds owner rights. How to set up pydantic ai sql serv
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 pydantic ai sql server permissions by creating a dedicated database user. Then you assign that user to a read-only database role. Many engineering teams rush this step during early testing. They often give their AI agent a shared admin account to save time. This broad access creates a major risk for your production data. A single bad prompt can make the agent change or delete vital rows. It can even drop the whole database if it holds owner rights.
How to set up pydantic ai sql server permissions
First, you need to understand how logins and database users work together. A database user is the identity of a login inside one specific database. You have two main ways to create this identity for your agent. If the agent needs many databases on one server, use a standard login. You create this login in the master database first. Then you map it to a user inside each target database. If the agent only needs one database, use a contained database user. This user has a password and is not tied to a master login. An admin must enable contained databases on the server and database first. Microsoft supports thirteen types of database users for different needs.
Next, you assign your new agent user to a fixed database role. Every database role, including the public role, has basic connect rights. You cannot change the built-in rules assigned to fixed database roles. For an AI agent that answers questions, use the data reader role. Members of this role can read all data from all user tables. They can also read data from all user views in that database. Never put a read-only agent into the data writer role. That role lets the user add, delete, or change data in all tables. You must also avoid the database owner role for any AI tool. An owner can run all maintenance tasks and even drop the database.
What native database roles solve and what they miss
Fixed roles give you a strong starting point for database security. You can block write actions by using the deny data writer role. A member of that role cannot add, change, or delete any table data. You can also use the deny data reader role to block read access. Members of that role cannot read data from user tables or views. They cannot even read metadata about those tables and views. These built-in roles work well when you want simple, broad rules.
Still, native database roles only check who logs in at the start. They do not inspect what individual queries do while they run. A user in the data reader role can read every single user table. Your agent can pull emails, phone numbers, card numbers, or national IDs. Fixed roles cannot mask these fields in the query results on the fly. They also cannot pause a risky query to ask a human for approval. If an agent has write access for one table, it often gets more. The data writer role opens up write access across all user tables. Native roles lack fine controls for live AI agent traffic.
AI Agent Permissions + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Why agent tool calls need deeper query controls
AI agents build and run queries based on user text inputs. Two users can use the same app but need different data access. The database only sees the single login used by the AI app. It cannot tell which human user asked the agent a question. This makes audit logs hard to read during a security review. You see the app account in the log, not the actual person. Teams need a way to check every query before it hits the database. They also need a way to rewrite sensitive fields in the 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 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
Should I use a master login or a contained user for my agent?
Use a contained database user if the agent only reads one database. It keeps the user and password inside that single database. Use a login in master if the agent reads many databases on one server.
Can I edit the fixed data reader role to hide one secret table?
No, you cannot change the permissions assigned to fixed database roles. The data reader role always grants read access to all user tables and views. You must set up custom permissions or deny rules for specific tables.
What happens if I give my agent the database owner role?
The agent gains full control over the database setup and maintenance tasks. It can read, change, or delete any row in any table. Worse, a bad tool call could tell the server to drop the database.
Related guides
In this post