How to Configure Goose SQL Server Least Privilege
To set up goose sql server least privilege, create a dedicated database user with strict read-only rights. Then give that user role only to the tables your AI agent needs to read. Many teams start by giving Goose a shared admin account to test local workflows fast. This broad setup lets the AI agent change data or drop tables by mistake. A safer plan uses tight database roles to limit what the agent can do. How goose sql server least privilege works in the database SQL Server splits access in
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 least privilege, create a dedicated database user with strict read-only rights. Then give that user role only to the tables your AI agent needs to read. Many teams start by giving Goose a shared admin account to test local workflows fast. This broad setup lets the AI agent change data or drop tables by mistake. A safer plan uses tight database roles to limit what the agent can do.
How goose sql server least privilege works in the database
SQL Server splits access into server logins and database users. A database user is the identity of a login inside one specific database. When someone needs to reach many databases on one server, admins make a login in the master database. When an agent only needs one database, admins can make a contained database user with a password. First, an admin must turn on contained databases on the server and the target database. SQL Server supports thirteen types of database users in total.
After you make the user for Goose, you assign a role. Many teams pick the fixed reader role so the agent can run select queries. Members of this fixed reader role can read all data from all user tables and views. You can also add thefixed deny writer role so the user cannot add, change, or delete data. Do not give the agent the owner role. The owner role can run all maintenance tasks and can even drop the whole database. You cannot change the permissions built into fixed database roles. Every role, including the public role, includes the basic right to connect.
Why fixed roles alone leave a gap for AI agents
Native roles are a good first step for database safety. They stop an agent from running write queries if you only grant read rights. They also keep the agent out of other databases on the same server instance. Still, native roles check who logs in, not what each query does in flight. The fixed reader role lets Goose read every single user table and view in that database.
This creates a problem when tables hold private customer data. If a table holds emails, phone numbers, card numbers, or national IDs, a basic reader role returns them. Data masking rewrites matched fields in results before they reach the local machine. Also, a static database user relies on a password stored on the user machine. If an engineer puts that password in a local file for Goose, the key stays there until rotated. Finally, when ten engineers share one database user for their agents, database logs only show that single shared name. You cannot see which person ran a heavy query or why the agent asked for that data.
Least Privilege Principle + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Scoping table rights and watching agent queries
To tighten native security, skip the broad fixed reader role for sensitive databases. Instead, make a custom role and grant read rights only on safe views or specific tables. You can also use the fixed deny reader role to block access to all user tables and views when needed. Even with custom roles, database admins still have to manage passwords and review query logs by hand. Teams need a way to inspect every query from Goose and tie it to a real person.
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
What happens if Goose uses the database owner role?
The fixed owner role can run all setup and maintenance actions. It can also drop the entire database if the AI agent generates a bad command.
Does the fixed reader role hide sensitive columns from Goose?
No, members of the fixed reader role can read all data from all user tables and views. You must use custom grants or data masking, which rewrites matched fields in results.
Should I use a master login or a contained user for Goose?
Use a contained user with a password when Goose only needs one or a few databases. This avoids creating a server login in the master database.
Related guides
In this post