How to Configure LlamaIndex SQL Server Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a llamaindex sql server prevent delete rule, assign a read-only database role. You must also block write rights on the account that your query engine uses. Teams often build text-to-SQL apps with broad owner accounts during testing. When those broad accounts reach production, one bad prompt can wipe out live tables. How a llamaindex sql server prevent delete setup works LlamaIndex connects to relational databases through a light wrapper around SQLAlchemy. Its natural language query

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 llamaindex sql server prevent delete rule, assign a read-only database role. You must also block write rights on the account that your query engine uses. Teams often build text-to-SQL apps with broad owner accounts during testing. When those broad accounts reach production, one bad prompt can wipe out live tables.

How a llamaindex sql server prevent delete setup works

LlamaIndex connects to relational databases through a light wrapper around SQLAlchemy. Its natural language query engine turns user questions into raw SQL statements. If you do not pass a table list, the engine pulls all schema context. The engine then runs the generated SQL against your database.

Official LlamaIndex docs warn that running arbitrary SQL carries a real security risk. The docs advise teams to use restricted roles, read-only databases, or sandboxes. In SQL Server, you enforce these limits through logins and database users. A database user represents the identity of a login inside a specific database.

For access to many databases, admins create a SQL user tied to a master login. For one or a few databases, admins can set up a contained database user. This user holds a password and does not tie back to a master login. First, an admin must turn on contained databases on the server and database.

Once the user exists, you control table actions through fixed database roles. You cannot change the base permissions assigned to these fixed roles. Every role, including the public role, includes basic connect rights. To allow reads without deletes, place the agent user in the reader role.

Members of this reader role can read all data from all user tables and views. Make sure the user is not in the writer role or the owner role. The writer role lets a user add, change, or delete data in user tables. The owner role allows all maintenance tasks and can even drop the whole database.

For stricter safety, add the agent user to the deny writer role. Members of this role cannot add, change, or delete any table data. This step blocks delete commands even if another group grants write access.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native roles solve and what they leave open

Fixed database roles stop destructive commands at the storage level. If the language model writes a delete statement, SQL Server rejects the query. This native setup protects your tables from accidental drops and row deletions. It gives your team a solid baseline for read-only agent workflows.

Still, database roles only check who logs in at the start of a session. They do not inspect what individual queries do while in flight. A read-only user can still read every column in every user table and view by default. If you forget to limit the table list, the engine pulls the whole schema.

This means the model can still pull sensitive rows from tables it can read. Native roles cannot rewrite matched fields like emails, phone numbers, or card numbers in results. They also cannot pause a slow, heavy select query before it locks up CPU. Finally, shared SQL logins make it hard to see which person asked the question.

Limiting schema scope and sensitive reads

To reduce risk further, always pass a strict table list to the query engine. This stops the framework from pulling schema context for private tables. You can also place the agent user in the deny reader role for secret databases. Members of that role cannot read data or metadata from user tables and views.

Even with a small table list, allowed tables often hold personal data. National IDs, emails, and phone numbers often sit right next to safe business data. Database roles work at the object level and do not mask fields in flight. Teams must pair role limits with query checks to keep private data safe.

Add runtime guardrails beside SQL Server

Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.

With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.

Frequently asked questions

Can LlamaIndex block delete queries on its own?

No, the framework runs the SQL that the language model writes through SQLAlchemy. Its docs tell teams to use read-only databases or restricted roles for safety.

Which SQL Server role stops an agent from deleting rows?

Assign the user to the fixed reader role and the deny writer role. The deny writer role stops the user from adding, changing, or deleting any data.

Why should we avoid the owner role for text-to-SQL apps?

The fixed owner role allows all setup and maintenance actions inside the database. A user in this role can delete rows, change schemas, or drop the database.

The query runs. The emails stay hidden.