How to Configure Haystack SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up haystack sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role for your AI pipeline. Many teams start by giving their Haystack app a broad database owner account. That choice creates big risks when an AI agent generates and runs unexpected queries. A single bad query can change rows or drop an entire production database. How to set up haystack sql server read only access First, you must choose the right type of da

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 haystack sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role for your AI pipeline. Many teams start by giving their Haystack app a broad database owner account. That choice creates big risks when an AI agent generates and runs unexpected queries. A single bad query can change rows or drop an entire production database.

How to set up haystack sql server read only access

First, you must choose the right type of database user for your setup. SQL Server supports thirteen different types of database users. A database user represents the identity of a login inside a specific database. If your app needs many databases on one instance, create a login in master. Then map a database user to that login inside each target database.

For one or a few databases, you can use a contained database user instead. This user has a password and does not tie to a login in master. An admin must enable contained databases on the server and database first. Once the user exists, every role including public already has permission to connect.

Next, you place your new Haystack user into the fixed reader role. You add the user to this role using the standard alter role command. Members of this reader role can read all data from user tables and views. You should also add the user to the fixed role that denies data writes. Members of that deny role cannot add, change, or delete any table data. Fixed database roles have set permissions that an admin cannot change.

Why native roles are not enough for AI pipelines

Native roles stop your Haystack pipeline from changing or deleting table rows. They also stop the app from running maintenance tasks or dropping the database. This setup solves the basic problem of accidental writes from AI text-to-SQL components.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, fixed database roles leave a big gap during daily operations. The native reader role lets the user read all user tables and views. Your Haystack app might only need three tables to answer user questions. Yet the role exposes every table in that database to the AI model. If a prompt tricks the model, it can query sensitive customer tables.

You can block specific tables or views, or add the deny reader role. Members of the deny reader role cannot read data or metadata from user tables. Still, database roles only decide who logs in and which tables they read. They do not inspect what individual queries do while they run in flight. A read query can still pull emails, phone numbers, card numbers, or national IDs. Without query-level checks and data masking that rewrites matched fields in results, sensitive data flows into model prompts.

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 is the difference between a SQL login and a contained user?

A standard SQL user links to a server login stored in the master database. It works well when one account needs access to many databases on an instance. A contained user lives only inside the target database and has its own password. It works well when Haystack only needs one or a few databases.

Can I edit the permissions of the fixed database reader role?

No, SQL Server does not let you change permissions on fixed database roles. The built-in reader role always grants read access to all user tables and views. If you need custom table rules, you must create a user-defined database role instead.

Why should I avoid the database owner role for Haystack?

Members of the owner role can run all setup and maintenance actions. They can edit data in all user tables and even drop the database. Using this role for an AI app puts all your data at risk.

The query runs. The emails stay hidden.