How to Configure Goose SQL Server Prevent Delete
You set up goose sql server prevent delete rules by assigning strict database roles. You restrict the agent user so it cannot remove rows from any table. Teams often connect AI agents to databases using shared admin credentials for speed. Those broad roles let an automated tool wipe out whole tables by mistake. One bad prompt can trigger a delete statement across critical records. Limiting what the database user can do stops data loss before it starts. How to set up goose sql server prevent de
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 prevent delete rules by assigning strict database roles. You restrict the agent user so it cannot remove rows from any table. Teams often connect AI agents to databases using shared admin credentials for speed. Those broad roles let an automated tool wipe out whole tables by mistake. One bad prompt can trigger a delete statement across critical records. Limiting what the database user can do stops data loss before it starts.
How to set up goose sql server prevent delete roles
SQL Server manages permissions through logins and database users. A database user is the identity of a login inside one specific database. When an admin needs access to many databases, a login in master is common. For just one or a few databases, a contained database user works well. This 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 database. SQL Server supports thirteen types of database users in total.
Once you create a dedicated user for Goose, you assign database roles. SQL Server includes fixed database roles with preset rules that you cannot change. Every role, including the public role, has basic rights to connect. 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. They cannot add, change, or remove records by default.
Sometimes teams put an agent in the data writer role by mistake. Members of the data writer role can add, delete, or change data everywhere. Even worse, members of the owner role can run all maintenance tasks. That owner role can even drop the whole database in SQL Server. To block all writes, you can add the user to the deny data writer role. Members of that role cannot add, modify, or delete any data at all.
Why native roles are not enough when Goose needs write rights
Fixed database roles work well when an AI agent only reads tables. The trouble starts when Goose needs to insert new rows or update records. Fixed roles group adding, changing, and deleting data together under one role. You cannot change the permissions assigned to fixed database roles in SQL Server. If you use the deny data writer role, you block inserts and updates too.
SQL Injection Prevention + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Database admins can build custom roles that allow inserts while denying deletes. Still, managing table permissions across dozens of schemas takes a lot of time. Static roles also decide who logs in, not what individual statements do in flight. If an agent has update rights, it can still overwrite rows with blank values. Native roles also do not rewrite sensitive fields like emails or phone numbers. Every query result returns raw card numbers or national IDs to the tool.
Managing agent database traffic in flight
Safe automation needs more than static user roles inside the database engine. Teams need a way to check each query before the server runs it. When you inspect statements in flight, you can block delete commands right away. At the same time, you can allow safe read or insert queries through. You can also pause risky updates until a human reviews and approves them. Data masking can rewrite matched fields in results, like emails and card numbers. This keeps live data safe while giving Goose the context it needs.
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 fixed roles in SQL Server block deletes while allowing inserts?
No, you cannot change the permissions assigned to fixed database roles. The data writer role allows adds, changes, and deletes all together. The deny data writer role blocks all three actions across user tables. You must set up custom permissions or check queries in flight instead.
What happens if Goose runs under the database owner role?
Members of the owner role can run all setup and maintenance activities. This role can even drop the entire database in SQL Server. You should never assign the owner role to an automated agent or script.
Should I use a standard login or a contained user for Goose?
Use a standard login in master if the agent needs many databases. If Goose only uses one database, a contained user is a good option. An admin must first enable contained databases on the server and database.
Related guides
In this post