How to Configure Haystack SQL Server Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a haystack sql server prevent delete rule, restrict the database user role. You assign read-only roles or custom grants so the AI pipeline cannot remove rows. Teams often start by giving a Haystack app a broad database login. When that login has owner or writer rights, a bad prompt can wipe out tables. Locking down the SQL user stops those destructive statements before they change data. How to set up haystack sql server prevent delete in the database SQL Server manages permissions t

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 a haystack sql server prevent delete rule, restrict the database user role. You assign read-only roles or custom grants so the AI pipeline cannot remove rows. Teams often start by giving a Haystack app a broad database login. When that login has owner or writer rights, a bad prompt can wipe out tables. Locking down the SQL user stops those destructive statements before they change data.

How to set up haystack sql server prevent delete in the database

SQL Server manages permissions through logins and database users. A database user is the identity of a login inside one specific database. When an account needs many databases, admins create a login in the master database. For just one database, admins can enable and use a contained database user instead.

Once you create the user for Haystack, you assign its role. SQL Server offers fixed database roles with permissions that you cannot change. If your Haystack pipeline only reads data, use the standard data reader role. Members of this role can read all data from all user tables and views.

Sometimes an AI agent needs to insert new rows or update existing records. The fixed data writer role lets a user add, change, or delete table data. Because you cannot change fixed roles, the data writer role always allows deletes. If you also assign the deny data writer role, you block all writes completely.

To allow inserts and updates while blocking deletes, avoid the fixed data writer role. Instead, create a custom database role for the Haystack user. Grant read, insert, and update rights on the target tables to this custom role. Then apply an explicit deny rule for delete actions on those same tables. In SQL Server, an explicit deny overrides other grants and blocks row removal.

What native roles solve and what they leave open

Native SQL Server roles do a great job of enforcing broad table rules. A reader role or custom role stops the Haystack account from deleting rows. Even if the language model writes a delete query, the database rejects it. You also avoid the database owner role, which can drop the entire database.

Still, static database roles only decide who logs in and which tables they touch. They do not inspect what individual queries do in flight. First, a read-only user can still read every column in every user table by default. That means the Haystack pipeline can pull raw emails, phone numbers, and card numbers. Without field masking, those sensitive values flow straight into the model prompt.

SQL Injection Prevention + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Second, a custom role that allows updates can still ruin your production data. A confused agent cannot delete a row, but it can overwrite every row with blank values. Native roles cannot pause a risky update to ask a human for approval. Third, storing a static SQL password in your app settings creates a key risk. If that password leaks, anyone can use it to query your tables.

Why query-level controls matter for AI pipelines

AI pipelines do not behave like normal app code with fixed queries. A language model builds new SQL statements on the fly from user text. Because each prompt is different, static database grants are only the first step. Teams also need a way to check each statement before it runs.

When you check queries in flight, you can block bad updates and deletes. You can also rewrite results to mask national IDs or card numbers by default. This keeps your data safe while letting Haystack answer user questions.

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 fixed data writer role to block deletes?

No, SQL Server does not let you change permissions on fixed database roles. The data writer role always includes the right to add, change, and delete data. To block deletes while allowing inserts, use a custom role with specific grants.

Does the deny data writer role stop only delete statements?

No, the deny data writer role blocks all write actions on user tables. Members in this role cannot add, modify, or delete any data at all. Use it when you want to make sure a user stays strictly read-only.

Should Haystack use the database owner role for testing?

Avoid using the database owner role for any AI pipeline, even in test setups. Members of this role can run all maintenance tasks and drop the whole database. Give the Haystack user a reader role or a tightly scoped custom role instead.

The query runs. The emails stay hidden.