How to Configure Mastra SQL Server Permissions
You set up mastra sql server permissions by making a dedicated database user. Then you put that user in a narrow fixed role for your AI agent. Many teams start by reusing a broad admin login to save time during tests. That shortcut lets an automated agent change or delete tables across the whole server. If a prompt goes wrong, an open role turns a bad query into lost data. How mastra sql server permissions work at the database level SQL Server splits login checks into server identities and da
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 mastra sql server permissions by making a dedicated database user. Then you put that user in a narrow fixed role for your AI agent. Many teams start by reusing a broad admin login to save time during tests. That shortcut lets an automated agent change or delete tables across the whole server. If a prompt goes wrong, an open role turns a bad query into lost data.
How mastra sql server permissions work at the database level
SQL Server splits login checks into server identities and database identities. A database user is the identity of a login inside one specific database. If an agent needs many databases on one instance, teams make a standard login. That login lives in the master database and maps to a user per database. If the agent only needs one database, a contained database user works well. A contained user has its own password and does not need a master login. An admin must turn on contained databases on the server and database first. SQL Server offers thirteen types of database users to match different identity setups.
Once the user exists, you grant rights through fixed database roles. Every role, including the basic public role, gives the user permission to connect. You cannot change the built-in rules assigned to these fixed database roles. For an agent that only answers questions, teams use the data reader role. Members of this role can read all data from all user tables and views. If the agent must save records, the data writer role allows table changes. Members of the data writer role can add, delete, or change data everywhere. You should avoid the database owner role for any automated agent workload. The owner role allows all maintenance tasks and can even drop the database.
What native roles solve and where gaps remain
Native roles do a good job of stopping unauthorized logins at the door. They also split read workloads from write workloads across your main user tables. If an agent only has read rights, it cannot drop a table by mistake. Teams can also pair positive roles with deny roles to tighten table rules. The deny data writer role blocks a user from adding, changing, or deleting rows. The deny data reader role blocks all reads on user tables and views. It even hides the metadata about those tables and views from the user.
Yet database roles only decide who logs in and which broad actions work. They do not inspect what individual statements do while a session runs. A user in the data reader role can still read every single user table. That means the agent can pull sensitive emails, phone numbers, or card numbers. It can also pull national IDs stored inside standard customer or billing tables. Native roles cannot rewrite matched fields in results before they reach the model. Also, a user in the data writer role can wipe rows in any table. Fixed roles cannot pause a dangerous delete statement and ask a human first.
Kubernetes API Server Access + AI Agent Permissions: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Scoping agent queries beyond static database roles
To keep production data safe, teams should avoid giving agents broad role rights. Start by mapping out the exact tables your Mastra workflows need to read. If a workflow only reads public catalog data, keep it away from user accounts. Create a separate database user for each distinct agent or workflow in your app. That way, a single agent never holds more rights than its task requires.
Even with split users, static database roles remain an all-or-nothing check at login. They do not adapt to the context of an individual prompt or query. Teams running agents on live data need controls that work on each query. Checking statements in flight lets you block risky writes and mask private fields.
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 use a login or a contained user for Mastra?
Use a contained user if your Mastra agent only uses one database. Use a standard login in master if the agent reads across many databases.
Can the fixed data reader role hide specific sensitive columns?
No, the fixed data reader role grants read rights to all user tables. You cannot change the built-in rules of fixed database roles in SQL Server.
How do I stop a Mastra agent from changing table data?
Put the agent user in the data reader role and skip the writer role. You can also add the deny data writer role to block all data changes.
Related guides
In this post