How to Configure Goose SQL Server Audit Log

Published
Length
4 min read

Luis Felipe Dias

All posts

You set up a goose sql server audit log by pairing server audit objects with dedicated database roles. This setup tracks every query that the AI agent sends to your database. Many teams start by giving the agent a broad role so it can work fast. Broad roles let the agent change or delete data by mistake. A clear role setup and a solid log plan stop bad changes before they spread. How the native goose sql server audit log and roles work To build a clean goose sql server audit log, start with 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 goose sql server audit log by pairing server audit objects with dedicated database roles. This setup tracks every query that the AI agent sends to your database. Many teams start by giving the agent a broad role so it can work fast. Broad roles let the agent change or delete data by mistake. A clear role setup and a solid log plan stop bad changes before they spread.

How the native goose sql server audit log and roles work

To build a clean goose sql server audit log, start with the agent identity. A database user is the identity of a login when it connects to a database. If the agent needs many databases on an instance, teams use a login in master. If the agent needs one or a few databases, a contained database user works well. A contained user has a password and does not tie to a login in master. An admin must first turn on contained databases on the server and the database. SQL Server supports thirteen types of database users for these setups.

Once the user exists, you assign a fixed database role to control access. All roles, including the public role, have basic connect rights. You cannot change the permissions assigned to fixed database roles. If the agent only needs to inspect data, use the data reader role. Members of that role can read all data from all user tables and views. If you add the data writer role, the agent can add, delete, or change data. You can also use deny roles to block read or write rights across user tables. Never give an agent the owner role. The owner role can run all maintenance tasks and even drop the database.

With the user and role in place, the native server audit tracks that specific login. The database engine writes an entry to the log each time the agent runs a query. Security teams read these logs later to see which tables the agent touched.

What native roles solve and what they leave open

Native roles and server logs solve the first layer of database security. They decide who can log in and which broad actions they can take. A read role stops an agent from dropping a table or changing rows. A server audit log gives you a historical record for review after an incident happens.

Still, native tools leave a large gap during live agent runs. Fixed roles are all or nothing across every user table in the database. A member of the data reader role can read every single table and view. If a table holds phone numbers, emails, or card numbers, the agent reads them all. The native audit log records that the query ran, but it does not mask results. It cannot rewrite matched fields in results before the agent sees the output.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native setups also rely on credentials that sit on the user machine. If an engineer puts a database password in a local file, that secret can leak. Also, the database login only sees the shared service account if teams share keys. It cannot link a single query back to the human who prompted the agent. And if an agent has write rights, native roles cannot pause one risky delete command. The database just checks the role, runs the query, and writes to the log.

Why query level controls matter for AI agents

AI agents like Goose run queries much faster than human engineers. A human might run five queries in an hour to debug an issue. An agent can run fifty queries in a minute while exploring a schema. Relying on a post-incident audit log is too late when an agent moves that fast.

Teams need controls that inspect each statement in flight rather than just checking the login. When you check queries in flight, you can block bad commands before they hit data. You can also mask emails, phone numbers, and national IDs in the query output. This keeps sensitive fields out of the agent context window while keeping the workflow fast.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server. It reads plain batches and parameterized queries on the TDS wire while passing Windows login through untouched.

Before a batch hits the server, hoop.dev guardrails check the statement against your rules. Disallowed actions get a native TDS error with your message. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns that match your rules. Every query and decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can I limit the fixed data reader role to just a few tables?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. Members of the fixed data reader role can read all data from all user tables and views.

Should I use a standard login or a contained user for Goose?

Use a contained user if the agent only needs access to one or a few databases. Use a login in master if the agent needs access to many databases on an instance.

Does a native SQL Server audit log hide sensitive data from the agent?

No, a native audit log only records that a query ran against the database. To hide emails, card numbers, or phone numbers, you must rewrite matched fields in results in flight.

The query runs. The emails stay hidden.