How to Configure Pydantic AI SQL Server Least Privilege
To set up pydantic ai sql server least privilege, give your agent a dedicated database user. Then limit that user to specific table grants or read roles. Many teams start by giving an agent the owner role to build fast. That choice lets a single bad query alter tables or drop the whole database. How pydantic ai sql server least privilege works in practice Your Python agent needs a safe identity before it runs any query. In SQL Server, a database user is the identity of a login inside a databa
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 least privilege, give your agent a dedicated database user. Then limit that user to specific table grants or read roles. Many teams start by giving an agent the owner role to build fast. That choice lets a single bad query alter tables or drop the whole database.
How pydantic ai sql server least privilege works in practice
Your Python agent needs a safe identity before it runs any query. In SQL Server, a database user is the identity of a login inside a database. When an app needs many databases on one instance, teams create a login in master. They then map a SQL user to that login.
If your agent only needs one database, you can use a contained database user. This is a SQL user with a password that does not link to master. An admin must enable contained databases on the server and database first. Microsoft supports thirteen types of database users in total.
Next, you must set up the right role for that user. All roles in the database, including the public role, have basic permission to connect. You cannot change the rules built into fixed database roles. For read tasks, many teams add the agent user to the fixed data reader role. You can also add the user to the deny data writer role. That role stops the agent from adding, changing, or deleting data in user tables.
Why native roles are only the first step for agent safety
Fixed roles are fast to set up, but they are often too broad. Members of the data reader role can read all data from all user tables. They can also read every view in that database. If a table holds emails, phone numbers, or card numbers, the agent gets them all. A custom role with grants on a few tables helps reduce this risk. You can also add the deny data reader role to block table reads and metadata.
Even with tight table grants, native roles only check who logs in. They do not inspect what each query does while it runs. An agent with read access to a customer table can still pull every row. It can also read sensitive national IDs or card numbers from allowed columns. Native database roles cannot mask fields like emails or phone numbers in query results. They also cannot pause a risky query for human approval before it runs.
Least Privilege Principle + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
How to scope agent queries and protect sensitive columns
To keep data safe, start by skipping the fixed data reader and data writer roles. Create a custom database role for your agent instead. Grant that role read access only to the exact views or tables it needs. Leave out tables that hold secrets, billing records, or raw user profiles.
Next, separate your read agents from your write agents. A support bot that checks order status never needs write permissions. Give it a user that belongs to the deny data writer role. If another agent must update rows, give it a separate user with narrow table grants. Never assign the database owner role to an AI agent. Members of the owner role can change settings and drop the whole database.
Finally, plan for what happens after the database accepts the agent login. Static grants cannot rewrite sensitive text inside an allowed column. You need a way to inspect queries in flight and mask private fields 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 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
What happens if I give my Pydantic AI agent the data reader role?
The agent can read every row from all user tables and views in that database by default. It will pull sensitive fields like emails or card numbers if they sit in those tables.
Should I use a contained database user for an AI agent?
Yes, a contained user works well when the agent only needs one database. It keeps the user and password inside that database instead of the master database.
Can I edit the permissions of the built-in SQL Server roles?
No, SQL Server does not let you change the permissions of fixed database roles. If a fixed role is too broad, create a custom role with specific table grants.
Related guides
In this post