How to Configure Goose SQL Server Approval
You set up goose sql server approval by pairing agent tool prompts with strict database roles. This step stops the AI agent from running bad queries on its own. Many teams start by giving the agent a shared login to test tasks fast. Broad roles let the agent change or delete table rows before a human can react. A safer setup uses tight database roles and human sign-off for every risky command. How native goose sql server approval and database roles work First, you need to set up the right ide
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 goose sql server approval by pairing agent tool prompts with strict database roles. This step stops the AI agent from running bad queries on its own. Many teams start by giving the agent a shared login to test tasks fast. Broad roles let the agent change or delete table rows before a human can react. A safer setup uses tight database roles and human sign-off for every risky command.
How native goose sql server approval and database roles work
First, you need to set up the right identity in the database. In SQL Server, a database user is the identity of a login when it connects. You can create a standard login in the master database for wide use. For one or a few databases, you can use a contained database user with a password. An admin must enable contained databases on the server and the database first. Microsoft supports 13 types of database users for different needs.
Next, you assign that user to a fixed database role. The db_datareader role lets the user read all data from all user tables and views. If the agent must block writes, you can add the db_denydatawriter role. Members of db_denydatawriter cannot add, change, or delete any table data. If the agent needs to write, teams often look at the db_datawriter role. That role lets a user add, delete, or change data in all user tables. Avoid the db_owner role for AI agents. A db_owner member can run all maintenance tasks and even drop the database. Note that you cannot change the permissions assigned to fixed database roles.
Why native roles and local prompts leave a gap
Native roles and local agent prompts solve only half of the safety problem. Database roles check who logs in, not what a single query does in flight. If you give the agent db_datawriter for one task, it can edit every user table. If you give it db_datareader, it can read every table and view at once. That includes tables holding emails, phone numbers, card numbers, and national IDs. You would have to add db_denydatareader to block all table reads and metadata.
On the client side, the Goose agent runs on the developer machine. A user can turn off local confirmation prompts to make their work go faster. Even when prompts stay on, reviewers often click approve without reading long SQL text. Also, local setups mean a database password or key sits on the laptop. If a prompt injection hits the agent, it uses those local rights right away. Native tools do not pause a single risky query for a second person to check. They also do not rewrite sensitive fields in the query results.
Approval Chains & Escalation + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Planning a safer approval workflow for AI queries
To fix this gap, teams move approval checks out of the local laptop. The agent should only hold read rights by default. When the agent wants to change data, the request should pause for team review. A reviewer in Slack or Teams can then check the exact SQL statement. After one-time approval, the query runs and the session closes. For read queries, masking rules can rewrite emails or card numbers in the results. This keeps sensitive data safe while the agent helps the team debug issues.
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 SQL Server role is safest for a Goose agent?
Start with the db_datareader role and pair it with db_denydatawriter. This lets the agent read user tables while blocking all data changes and deletes.
Can I customize the permissions inside db_datareader or db_datawriter?
No, SQL Server does not let you change permissions inside fixed database roles. To limit table reads or writes, you must build custom roles instead.
Why is local agent approval not enough for production databases?
Local prompts rely on the person running the agent to spot bad SQL code. Moving approvals to a shared team channel adds a real check before writes run.
Related guides
In this post