How to Configure Dify SQL Server Approval
You set up dify sql server approval by pairing strict database roles with human checks. This workflow stops AI agents from running unsafe queries on your tables without permission. Engineering teams often build AI workflows that query or update company data. To move fast, teams sometimes give the AI app broad database accounts. An account in the db_owner role can run any maintenance task and even drop the database. When an AI agent holds those rights, one bad prompt can wipe out production tabl
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 dify sql server approval by pairing strict database roles with human checks. This workflow stops AI agents from running unsafe queries on your tables without permission.
Engineering teams often build AI workflows that query or update company data. To move fast, teams sometimes give the AI app broad database accounts. An account in the db_owner role can run any maintenance task and even drop the database. When an AI agent holds those rights, one bad prompt can wipe out production tables.
How native dify sql server approval and roles work
A safe setup starts inside the database engine with proper user accounts. A database user is the identity of a login when it connects to a database. If your app needs many databases on an instance, you use a login in master. If your app needs just one database, you can use a contained database user. An admin must first enable contained databases on the server and the database itself. In total, the database engine supports thirteen different types of database users.
After you create the user, you assign it to fixed database roles. Every role, including the public role, starts with basic rights to connect. Members of the db_datareader role can read all data from all user tables and views. Members of db_datawriter can add, delete, or change data across all user tables. You cannot change the permissions assigned to these fixed database roles. If you must block access, you can use two specific deny roles. The db_denydatareader role blocks reading any data or metadata from user tables and views. The db_denydatawriter role stops a user from adding, changing, or deleting any data.
Inside your AI workflow builder, you split read tasks from write tasks. You let the agent use a read role to answer basic user questions. When the agent wants to change data, the workflow pauses for a human check. A team member reviews the generated SQL text before the app runs it.
Where native roles and workflow checks fall short
Fixed database roles and app workflow pauses solve the first layer of risk. They control which account logs in and when a workflow step moves forward. Still, they leave an operational gap once a query actually runs in flight.
Approval Chains & Escalation + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
First, fixed roles apply to whole databases rather than single sensitive columns. A user in db_datareader can read every single user table and view. If a table holds emails, phone numbers, card numbers, or national IDs, the agent reads them. Without data masking that rewrites matched fields in results, raw sensitive data reaches the model.
Second, app-level pauses only check the workflow state inside the AI builder. They do not inspect the actual database session at the network level. If an engineer reuses the write credentials outside the workflow, no approval check happens. A user in db_datawriter can still delete rows from any user table at any time. Database admins must then dig through server logs to see which prompt caused the change.
Building a safer query review workflow
To close these gaps, teams separate query generation from query execution. The AI agent should only hold rights to draft queries or read safe views. When the agent needs to run a write command, that request should wait for review. The reviewer needs to see the exact statement, the target database, and the user identity. Adding field masking on query results also rewrites emails and card numbers before models process them.
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 edit the permissions of the db_datareader role for my AI agent?
No, you cannot change the permissions assigned to fixed database roles in SQL Server. If db_datareader is too broad, you must create a custom role with specific table rights.
Should I use a standard login or a contained user for Dify?
Use a contained database user if the app only needs one or a few databases. Use a login in master if the app must reach many databases on the instance.
How do I stop an AI database user from changing table rows?
Do not add the user to db_datawriter or db_owner. You can also add the user to db_denydatawriter to block all adds, edits, and deletes.
Related guides
In this post