How to Configure Goose SQL Server Permissions
To set up goose sql server permissions, create a dedicated database user for the agent. Then assign that user only the minimal read or write role needed for your task. Many teams start by giving their coding agent full owner rights on a database. That broad role lets the agent run any query, change schemas, or delete whole tables. A smaller role keeps your data safe when an automated tool writes bad SQL. How goose sql server permissions work at the database level In SQL Server, a database use
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.
To set up goose sql server permissions, create a dedicated database user for the agent. Then assign that user only the minimal read or write role needed for your task. Many teams start by giving their coding agent full owner rights on a database. That broad role lets the agent run any query, change schemas, or delete whole tables. A smaller role keeps your data safe when an automated tool writes bad SQL.
How goose sql server permissions work at the database level
In SQL Server, a database user is the identity of a login inside a database. Microsoft offers thirteen types of database users to handle different login needs. If Goose needs to reach many databases on one server, use a standard SQL login. This login lives in the master database and maps to a user in each database.
If Goose only needs one database, you can use a contained database user instead. A contained user has a password and does not need a login in master. An admin must turn on contained databases on the server and database first. Once the user exists, every role, including the default public role, grants connect rights.
Next, you add the Goose user to a fixed database role. You cannot change the rights built into these fixed database roles. The data reader role lets Goose read all data from all user tables and views. If Goose must edit rows, the data writer role lets it add, change, or delete data. Never assign the database owner role to an agent in a live system. Members of the owner role can do all maintenance tasks and even drop the database.
How to restrict tables with deny roles and custom roles
Sometimes the standard data reader role is still too wide for an AI agent. Remember that the data reader role opens every user table and view at once. Your database might hold payment tables or secret config tables alongside normal app data. SQL Server includes two fixed deny roles to block access across the board. The deny data reader role stops a user from reading any user tables or views. It also blocks the user from seeing metadata about those tables and views. The deny data writer role stops a user from adding, changing, or deleting any data.
Because fixed roles cover all tables, teams often make custom database roles for Goose. With a custom role, an admin grants read rights on safe tables only. You can also deny read rights on specific tables that hold private user fields. This setup keeps the agent away from tables it does not need to touch. You should also rotate the database password often and store it outside your code.
Kubernetes API Server Access + AI Agent Permissions: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Where native database roles fall short for AI agents
Native SQL Server roles decide who can log in and which tables they can read. They do not inspect what individual SQL statements do while the session is active. If Goose has the data reader role, it can read every row in a customer table by default. A single query can pull thousands of emails, phone numbers, card numbers, or national IDs. Those raw records then flow right into the model context window and local logs.
Write access creates an even bigger risk during an automated coding session. A user in the data writer role can delete or change rows in any user table. If the agent forgets a filter clause, one update statement can ruin a whole table. Static database roles cannot pause a dangerous query to ask a human for approval. They also cannot rewrite matched fields in query results to mask emails or card numbers. Safe agent workflows need controls that check each statement and filter results in flight.
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
Should I give Goose a SQL login in master or a contained user?
Use a contained database user if Goose only needs access to one or two databases. An admin must enable contained databases on the server and database first. Use a login in master if the agent must query many databases on one instance.
Can I edit the built-in data reader role to hide one table from Goose?
No, SQL Server does not let you change the rights assigned to fixed database roles. The data reader role always grants read access to all user tables and views. To hide specific tables, create a custom role or apply table-level deny rules.
Why is the database owner role dangerous for an AI agent?
The database owner role allows all config and maintenance work inside SQL Server. A user in this role can even drop the entire database with one command. Always stick to read-only roles or narrow custom roles when connecting automated agents.
Related guides
In this post