How to Configure Mastra SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up mastra sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role and give those credentials to your agent. Teams often start by reusing an existing app login to save time during early testing. If that login has broad rights, an agent tool call can change or delete production tables. How mastra sql server read only access works in SQL Server SQL Server separates server logins from database users. A database user is the

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 read only access by creating a restricted database user. Then you assign that user to a read-only database role and give those credentials to your agent. Teams often start by reusing an existing app login to save time during early testing. If that login has broad rights, an agent tool call can change or delete production tables.

How mastra sql server read only access works in SQL Server

SQL Server separates server logins from database users. A database user is the identity of a login when it connects to a database. If an agent needs many databases on one instance, admins create a SQL user with a login in master. If the agent only needs one database, a contained database user with a password is an option. An admin must first enable contained databases on the server and the database. SQL Server supports 13 types of database users in total.

Once the user exists, admins assign it to a fixed database role. Every role, including the public role, includes basic permission to connect. To allow reads, admins add the user to the datareader fixed role. Members of this role can read all data from all user tables and views. Admins can also add the user to the denydatawriter role to block writes. Members of that role cannot add, modify, or delete data in any user table.

You cannot change the permissions assigned to fixed database roles in SQL Server. You should never put an agent user into the database owner fixed role. Members of the owner role can run all maintenance tasks and even drop the database. After you set up the read-only user, you pass its credentials to your Mastra database tool.

What native roles solve and what they leave open

Native SQL Server roles solve the problem of unauthorized writes and schema changes. When your Mastra workflow connects as a datareader member, the database engine blocks write commands. An agent cannot drop a table or update rows by mistake.

Still, native database roles only check who logs in at the start of a session. They do not inspect what individual queries do while they run. Because the datareader role grants read access to all user tables, the agent can read everything. If a user table holds sensitive rows, a read query will return them to the agent.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native roles cannot rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in query results. They also cannot pause a risky query to ask a human for approval first. Also, the Mastra app still holds a static password or connection string in its settings. If that secret leaks, anyone can use it to read all user tables and views.

Tightening table access for agent workflows

Some teams avoid the fixed datareader role when a database holds sensitive tables. Instead, they create a custom role and grant read rights on specific tables or views. They can also use the denydatareader role to block reads across all user tables and views. Members of denydatareader cannot even read metadata about those tables and views.

Custom grants narrow which tables the Mastra agent can query. Even so, table grants still apply to the whole table and the whole login session. When a single table mixes safe columns with emails or card numbers, static roles fall short. Teams still need a way to mask sensitive fields in results and audit every query.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server. It reads plain batches and parameterized queries on the TDS wire while passing Windows login through untouched.

Before a batch hits the server, hoop.dev guardrails check the statement against your rules. Disallowed actions get a native TDS error with your message. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns that match your rules. Every query and decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Can I modify the permissions of the SQL Server datareader role for Mastra?

No, SQL Server does not let you change the permissions of fixed database roles. If the datareader role is too broad, create a custom database role and grant table permissions instead.

Should I use a standard login or a contained user for a Mastra agent?

Use a contained database user with a password when the agent only needs one or a few databases. This requires an admin to enable contained databases on both the server and the database first.

Does the SQL Server datareader role hide sensitive columns from Mastra?

No, members of the datareader role can read all data from all user tables and views. To hide specific fields like emails or phone numbers in results, you need dynamic data masking in flight.

The query runs. The emails stay hidden.