How to Configure Mastra SQL Server Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mastra sql server audit log by pairing database roles with query tracking. First, you create a dedicated database user for your AI agent and turn on server logging. Teams often start by giving an agent a single shared login to run queries. When that login has broad permissions, the agent can read or change every table. A simple prompt error can then expose private rows or break production data. How to set up a mastra sql server audit log Setting up a clean trail starts with how t

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 mastra sql server audit log by pairing database roles with query tracking. First, you create a dedicated database user for your AI agent and turn on server logging. Teams often start by giving an agent a single shared login to run queries. When that login has broad permissions, the agent can read or change every table. A simple prompt error can then expose private rows or break production data.

How to set up a mastra sql server audit log

Setting up a clean trail starts with how the agent logs in to the database. In SQL Server, a database user is the identity of a login inside a database. Across the engine, there are 13 types of database users you can pick from. If the agent needs many databases on one instance, teams use a standard SQL user. That user links back to a login stored in the master database.

If the agent needs one or a few databases, a contained user works well. A contained user has a password and does not tie to a master login. An admin must first turn on contained databases on the server and the database. Giving the agent its own distinct user makes the audit log much easier to read. Every query in the log will show the agent identity instead of a shared account.

Next, you assign a role to limit what the agent can do. SQL Server offers fixed database roles, and you cannot change the permissions inside them. All roles, including the public role, have basic permission to connect. Members of the db_datareader role can read all data from all user tables and views. If the agent must write data, db_datawriter lets it add, delete, or change rows. You should avoid giving the agent the db_owner role in production. A member of db_owner can run all maintenance tasks and even drop the database.

What native roles solve and what they miss

Native database roles and server logs solve the problem of who can log in. They also record which database user ran a statement after the fact. If you want to block table access, you can use deny roles. Members of db_denydatareader cannot read any data or metadata from user tables and views. Members of db_denydatawriter cannot add, change, or delete any data in user tables.

Still, fixed roles apply to all user tables at once. A user in db_datareader can read every column in every user table by default. Native roles decide who logs in, not what individual queries do in flight. The database cannot tell if a prompt tricked the agent into asking for sensitive rows. Server logs will record that the agent read the table, but only after it happens. Native roles also do not rewrite sensitive fields like emails or phone numbers in results.

Audit Log Integrity + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why agent queries need row and field checks

AI workflows build queries on the fly based on user input and context. Even a read-only agent can pull card numbers or national IDs from a user table. Standard database logs store the query text, yet they do not link to the user prompt. When an audit team checks the log, they only see the shared agent user. They cannot see which end user or workflow step triggered that specific database query.

To keep data safe, teams need controls that inspect each query before it runs. They also need data masking that rewrites emails or card numbers in query results. Combining clear identity tracking with field masking closes the gap left by broad database roles.

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 database role should a read-only Mastra agent use in SQL Server?

Use the db_datareader role so the agent can only read user tables and views. Pair it with db_denydatawriter to make sure the agent cannot add, change, or delete data.

Should I use a contained database user for an AI agent?

Yes, a contained user is a good choice if the agent needs one database. An admin must first enable contained databases on the server and the target database.

Why do SQL Server audit logs miss sensitive data leaks from agents?

Native logs record that a query ran, but they do not mask results in flight. A user in db_datareader can still pull emails, phone numbers, and card numbers into memory.

The query runs. The emails stay hidden.