How to Configure Goose SQL Server Read Only Access
You set up goose sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role before connecting your agent. Many teams start by giving their AI agent an existing admin login to save time. That broad account lets the agent change rows or drop tables by mistake. A safer setup uses native database permissions to block write queries from the start. How to set up goose sql server read only access natively First, an admin must choose how
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 read only access by creating a restricted database user. Then you assign that user to a read-only database role before connecting your agent. Many teams start by giving their AI agent an existing admin login to save time. That broad account lets the agent change rows or drop tables by mistake. A safer setup uses native database permissions to block write queries from the start.
How to set up goose sql server read only access natively
First, an admin must choose how the agent logs in to the server. A database user is the identity of a login when it connects to a database. If the agent needs many databases on one instance, use a login in master. You then map a SQL user to that login inside each target database. If the agent needs just one database, a contained database user is another option. A contained user has a password and does not tie to a login in master. An admin must enable contained databases on the server and the database first.
Next, the admin grants read permissions to the new database user. Every user starts in the public role, which includes basic connect permission. To allow read queries, you add the user to the fixed data reader role. Members of this role can read all data from all user tables and views. You cannot change the permissions assigned to fixed database roles. To be extra safe, you can also add the user to the deny data writer role. Members of that role cannot add, change, or delete data in any user table. Avoid the database owner role, because it allows all maintenance steps and can drop the database. Finally, you give the agent the new user name and password to connect.
What native read-only roles solve and what they leave open
Native database roles do a good job of stopping accidental writes and schema changes. When the AI agent sends an update or delete statement, the database rejects it. This setup protects your tables from bad prompts or broken agent loops. It also keeps the setup simple because you use built-in database controls.
Still, a fixed read role decides who logs in, not what queries do in flight. A user in the fixed data reader role can read every user table and view. That means the agent can still pull sensitive columns during a routine task. Query results may contain raw emails, phone numbers, card numbers, or national IDs. The database role has no way to mask those fields inside the returned rows. Also, sharing a static password with an agent desktop creates a secret management risk. If someone copies that password, they get the same broad read permissions across all tables. Native roles also lack statement-level checks to block huge full-table scans before they run.
Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Why query-level controls matter for AI agents
AI tools generate SQL fast and often explore schemas to find context. An agent might run a broad select query on a customer table just to check column types. With only a native read role in place, the database sends back real customer records. Those records then flow into the agent context and external model logs. Adding field-level masking rewrites matched fields like emails and card numbers in query results. This lets the agent inspect schemas and debug issues without pulling raw sensitive values.
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 I change the permissions of the built-in SQL Server data reader role?
No, SQL Server does not let you change the permissions of fixed database roles. If you need custom table rules, you must create a custom role and grant select permissions table by table.
Should I use a login in master or a contained user for Goose?
Use a login in master if the agent needs to read across many databases on the same server. Use a contained database user if you want to isolate the agent to one specific database.
Does the deny data writer role block schema changes too?
The deny data writer role only blocks adding, changing, or deleting rows in user tables. You must still keep the user out of owner or admin roles so it cannot alter tables or drop the database.
Related guides
In this post