How to Configure LangChain SQL Server Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up langchain sql server permissions by making a dedicated database user and role. Then you give that user read rights only on the tables your agent needs. Most teams start by sharing an existing app login with the agent. That quick shortcut often gives the AI model broad rights across the whole database. If a prompt tricks the agent, wide roles let it change or drop data. How langchain sql server permissions work SQL Server splits access into two layers: server logins and database use

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 langchain sql server permissions by making a dedicated database user and role. Then you give that user read rights only on the tables your agent needs. Most teams start by sharing an existing app login with the agent. That quick shortcut often gives the AI model broad rights across the whole database. If a prompt tricks the agent, wide roles let it change or drop data.

How langchain sql server permissions work

SQL Server splits access into two layers: server logins and database users. A database user is the identity of a login inside one specific database. When an agent needs access to many databases, teams make a login in master. Then they map that login to a user inside each target database. If the agent only needs one database, a contained user is an option. A contained user holds its own password and does not need a master login. An admin must turn on contained databases on the server and database first.

After you make the user, you assign database roles or table grants. SQL Server includes fixed database roles whose built-in rights cannot be changed. The data reader role lets a user read all data from all user tables. It also lets the user read from all views in that database. The data writer role lets a user add, delete, or change table data. The deny data writer role blocks a user from adding, changing, or deleting data. Every role, including the default public role, includes basic connect rights.

Why fixed roles leave gaps for AI agents

Using the fixed data reader role is fast, but it is often too broad. Members of that role can read every user table and view in the database. An AI agent converts natural language questions into SQL queries and runs them. If the agent uses the data reader role, it can query sensitive tables too. Those tables might hold customer emails, phone numbers, card numbers, or national IDs.

To limit the scope, admins skip the fixed data reader role. Instead, they create a custom role and grant select rights on chosen tables only. You can also add the user to the deny data writer role for safety. That stops the agent from running write queries even if other grants change later.

Native database roles decide who can log in and which tables they can touch. Still, static roles do not inspect what individual queries do while they run. A read-only user can still run a massive join that locks up CPU. Static grants also return raw emails and card numbers from allowed tables. The database does not rewrite matched fields in query results on the fly. Shared app credentials also make it hard to trace which prompt ran a query.

Kubernetes API Server Access + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Steps to lock down agent database access

Start by creating a dedicated login or contained user just for the AI agent. Never reuse the main owner role, because that role can drop the database. Next, build a custom role in the database instead of using fixed roles. Grant select rights only on the specific views or tables the agent needs. Add the agent user to the deny data writer role to block all changes.

Test the agent by logging in as that user and querying a blocked table. Make sure the database rejects the query and blocks any insert or delete commands. Review your database logs often to see which queries the agent builds and runs.

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

Should I give my LangChain agent the db_datareader role?

Only if every table in the database is safe for the agent to read. The data reader role grants read access to all user tables and views. For most databases, grant select rights on specific tables or views instead.

What is the difference between a SQL login and a contained user?

A standard SQL login lives in the master database and maps to database users. It works well when an account needs access to many databases on one server. A contained user stays inside one database and authenticates there with its own password.

How do I make sure an AI agent cannot change data in SQL Server?

Do not add the agent user to the data writer or owner roles. Give the user select rights only on the specific tables it needs. You can also add the user to the deny data writer role to block changes.

The query runs. The emails stay hidden.