How to Configure LlamaIndex SQL Server Least Privilege
You set up llamaindex sql server least privilege by creating a read-only database user. You also limit which tables the query engine can read. Many teams start by giving an AI agent a broad admin login. That approach lets generated SQL change or drop production tables by mistake. The framework docs notes that running arbitrary text-to-SQL queries is a security risk. It recommends using restricted roles, read-only databases, and sandboxing to lower that risk. How llamaindex sql server least pri
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 llamaindex sql server least privilege by creating a read-only database user. You also limit which tables the query engine can read. Many teams start by giving an AI agent a broad admin login. That approach lets generated SQL change or drop production tables by mistake. The framework docs notes that running arbitrary text-to-SQL queries is a security risk. It recommends using restricted roles, read-only databases, and sandboxing to lower that risk.
How llamaindex sql server least privilege works in practice
LlamaIndex uses a light wrapper around SQLAlchemy to talk to your database. Its natural language table query engine turns user questions into SQL statements. If you do not pass a list of tables, it pulls all schema context. Pulling every table wastes context tokens and exposes private tables to the model. Your first step is naming the exact tables the query engine may see. This step keeps the model focused on the right schema.
App settings alone cannot stop a bad query if the database user has write rights. You must also lock down the account inside SQL Server. A database user is the identity of a login when it enters a database. SQL Server offers thirteen types of database users for different needs. When an account needs many databases on one instance, teams create a login in master. When an app only needs one database, a contained database user is an option. Contained users have a password inside the database and do not need a master login. An admin must enable contained databases on the server and database first.
After you create the user, you must assign a strict role. Avoid the database owner role at all costs. That role can run all maintenance tasks and even drop the whole database. Many teams reach for the fixed data reader role instead. Members of the fixed data reader role can read all data from all user tables. They can also read all views in that database. You can pair it with the deny data writer role to block data changes. The deny data writer role stops the user from adding, changing, or deleting rows.
Why fixed database roles still leave a gap
Fixed database roles are simple to assign, and you cannot change their built-in permissions. Still, the fixed data reader role grants read rights to every single user table. If your database holds billing or payroll tables, the agent can still query them. Even if you hide a table name in LlamaIndex, the database role still allows reads. A prompt trick could push the model to query tables outside its list.
To tighten this setup, skip the fixed data reader role for your agent user. Grant select rights only on the specific tables and views the agent needs. You can also place the user in the deny data reader role for broad blocks. Wait to test each permission before you put the agent into production. This native setup controls who logs in and which tables they can read.
Least Privilege Principle + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Where native database roles fall short in flight
Database roles check permissions at login and table level, not row content in flight. Once a table is open to the agent, every column and row flows back. If a customer table holds emails, phone numbers, or card numbers, those values return in full. The model reads those raw values and may place them in its final answer. Native roles also cannot judge if a select query is too slow or wide. A generated query can join huge tables and lock up server memory.
Database logs also lose track of which human asked the original question. Every query from the app runs under the same shared service user. When an incident happens, teams struggle to match a bad SQL statement to a prompt. Closing this gap requires inspecting queries and masking sensitive fields like national IDs in results.
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
Why is the fixed data reader role often too broad for AI agents?
The fixed data reader role lets the user read all user tables and views. You cannot change the permissions inside a fixed role. Granting select permission on single tables is safer when sensitive tables share the database.
What happens if I do not specify tables in the LlamaIndex query engine?
If you omit the table list, the query engine pulls all schema context from the database. This exposes every table structure to the model and increases the chance of unwanted queries.
Should I use a standard login or a contained user for my agent?
Use a contained user if the agent only needs one or a few databases. It keeps the user and password inside that database instead of the master database. An admin must turn on contained databases on the server and database first.
Related guides
In this post