How to Configure Pydantic AI SQL Server Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a pydantic ai sql server audit log by pairing agent tracing with database events. This dual setup records both the agent prompt and the resulting database query. Engineering teams often give AI agents a single shared login to run queries. When that login has broad permissions, one bad prompt can read or alter sensitive tables. Worse, a shared login makes it hard to see which user or task triggered a specific query. How a pydantic ai sql server audit log works To build a pydantic ai

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 a pydantic ai sql server audit log by pairing agent tracing with database events. This dual setup records both the agent prompt and the resulting database query. Engineering teams often give AI agents a single shared login to run queries. When that login has broad permissions, one bad prompt can read or alter sensitive tables. Worse, a shared login makes it hard to see which user or task triggered a specific query.

How a pydantic ai sql server audit log works

To build a pydantic ai sql server audit log, you must understand database identities first. In SQL Server, a database user is the identity of a login when it connects. If an agent needs many databases on one instance, admins create a user with a master login. If the agent only needs one database, admins can use a contained database user instead. An admin must enable contained databases on the server and database first. Microsoft supports thirteen types of database users for different needs.

Once the user exists, admins assign a fixed database role to limit what the agent does. All roles, including the public role, have basic permission to connect. A read-only agent usually gets the data reader role. Members of this role can read all data from all user tables and views. If the agent must change records, admins assign the data writer role. That role lets the agent add, delete, or change data in all user tables. You cannot change the permissions assigned to these fixed database roles.

Some teams assign the database owner role during testing to move fast. That is a major risk because the owner role can do all maintenance and drop the database. Instead, admins can pair roles with deny roles to lock down the agent. A deny data writer role stops the user from adding, changing, or deleting any data. A deny data reader role stops the user from reading any user tables, views, or metadata.

What native database roles solve and what they miss

Native database roles and server logs solve the first layer of database security. They control which account can log in and which broad actions that account can take. Server logs also record when a database user runs a statement against a table. This helps security teams check activity after an incident happens.

Still, native setup rules only check who logs in at the start of a session. They do not inspect what individual queries do in flight before the database runs them. Because the data reader role grants read rights to all user tables, an agent can read everything. If a user table holds emails, phone numbers, card numbers, or national IDs, the agent gets them. Native roles cannot rewrite matched fields in results to hide sensitive values from the agent.

AI Audit Trails + Audit Log Integrity: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native database logs also lack context from the AI app layer. The database sees the shared service account, not the human or task behind the agent. Teams have to stitch app traces and database logs together by hand during an audit. Also, if an agent needs write permissions for one table, the standard data writer role covers all tables. You cannot pause a single risky delete statement for human approval using fixed roles alone.

Best practices for auditing AI database queries

Start by separating your AI agent accounts from human accounts and other app services. Give each agent workflow its own database user so your logs stay clear and easy to read. Never use the database owner role for an automated agent workflow in production. Combine the data reader role with the deny data writer role to enforce strict read-only behavior.

Next, log the agent task ID inside your app before the agent sends any SQL query. Pass that context along so your team can link every SQL statement to a specific prompt. Review your audit logs every week to spot unusual table scans or failed permission checks.

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 permissions of a fixed role in SQL Server?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. If the default data reader or data writer roles are too broad, you must create custom roles.

How do I stop a Pydantic AI agent from changing data?

Assign the agent user to the fixed data reader role and the deny data writer role. This lets the agent read user tables while blocking any attempt to add, change, or delete rows.

Why is a shared database login bad for AI agents?

A shared login hides which specific prompt, user, or workflow ran a given query. When every agent shares one identity, your audit log cannot trace a bad query back to its source.

The query runs. The emails stay hidden.