How to Configure Pydantic AI SQL Server Prevent Delete
To set up pydantic ai sql server prevent delete rules, assign read-only roles to your agent user. You can also deny delete rights on specific tables in the database. Teams often start by connecting their AI agent with a shared admin login. That broad role lets the agent run any query it generates. A bad prompt or model error can then wipe out key tables in seconds. Scoping down the database user is the first step to stop bad writes. How to set up pydantic ai sql server prevent delete rules SQ
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 prevent delete rules, assign read-only roles to your agent user. You can also deny delete rights on specific tables in the database. Teams often start by connecting their AI agent with a shared admin login. That broad role lets the agent run any query it generates. A bad prompt or model error can then wipe out key tables in seconds. Scoping down the database user is the first step to stop bad writes.
How to set up pydantic ai sql server prevent delete rules
SQL Server manages access through logins and database users. A login lets the app reach the server instance. A database user is the identity of that login inside one database. For access to many databases on an instance, teams create a login in the master database. For access to one or a few databases, teams can use a contained database user. A contained user has a password and does not tie to a login in master. An admin must first enable contained databases on the server and the target database.
Once the user exists, you must pick the right role. SQL Server offers fixed database roles with preset permissions that you cannot change. If the agent only needs to read data, use the data reader role. Members of this role can read all data from all user tables and views. If you give the agent the data writer role, it can add, change, or delete rows. Avoid the database owner role for AI agents at all costs. That role can run all maintenance tasks and can even drop the whole database.
Sometimes an AI agent needs to insert new rows or update records without deleting anything. Fixed roles cannot split those write tasks apart. The deny data writer role blocks adding, changing, and deleting data all at once. To stop only deletes, you must skip the fixed data writer role. Instead, grant select, insert, and update rights on the target tables. Then apply an explicit deny rule for the delete action on those same tables. In SQL Server, an explicit deny rule overrides any grant rule for that user.
Why native roles are not enough for AI agents
Native database roles solve who can log in and which tables they can touch. They work well when a single app needs the same rights all the time. If an agent tries to run a delete command, the database engine blocks it. This protects your rows from direct removal by that specific database user.
Still, static database roles leave big gaps in daily operations. A database role applies to the whole login session, not to individual queries in flight. If your agent needs to update rows, a bad query can still overwrite good data. It could set every column to blank values across a whole table. To the database, that looks like a valid update, not a delete.
AI Data Exfiltration Prevention + SQL Injection Prevention: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also do not protect sensitive fields during read queries. A user in the data reader role can read every column in every user table by default. When the agent runs a broad query, the database returns raw emails, phone numbers, and card numbers. You cannot pause a risky query for human approval before it runs using role settings alone. Managing hundreds of table-level grants across many databases also takes a lot of manual work.
How to check queries and mask data in flight
To close these gaps, teams add query checks and data masking between the agent and the database. Instead of relying only on static user grants, you inspect each statement before it runs. If the AI generates a destructive command or a broad update, the check stops it right away. You can also require a human on your team to approve risky writes from Slack.
Data masking handles the read side of the problem. As query results return to the agent, masking rewrites matched fields like national IDs or emails. This keeps sensitive strings out of model prompts and logs while letting the agent do its job.
Govern SQL Server queries and responses in flight
Table grants decide which SQL Server objects a caller may touch. They do not check query intent or rewrite sensitive values in results across every client and agent. By design, hoop.dev is an open-source proxy that sits next to SQL Server as a sidecar for your SQL Server. It inspects TDS batches and parameterized queries in real time.
With hoop.dev guardrails and hoop.dev data masking, banned statements are refused with your message. Matched sensitive fields in supported result columns are masked in memory, and each decision is logged per listener. Check out the code on GitHub.
Frequently asked questions
Can the fixed data writer role block delete commands?
No, the fixed data writer role allows adding, changing, and deleting data in all user tables. You cannot change the permissions assigned to fixed database roles. You must use custom permissions to allow inserts while blocking deletes.
How do I stop an AI agent from reading sensitive tables?
You can assign the deny data reader role to block all reads across user tables and views. For single tables, apply a table-level deny rule for select queries on those specific objects. You can also use dynamic data masking to rewrite emails and card numbers in query results.
Should my AI agent use a master login or a contained user?
Use a contained database user if the agent only needs access to one database. This keeps the user isolated to that database without needing a login in the master database. An admin must enable contained databases on the server and database first.
Related guides
In this post