How to Configure Mastra SQL Server Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mastra sql server prevent delete rule by limiting database user rights. Give your AI agent read and write rights, then block row removal in the database. Many teams start by giving their agent a broad admin role during tests. If that role stays in place, one bad prompt can wipe out whole tables. Fixing roles early stops AI tools from deleting real data by mistake. How to set up mastra sql server prevent delete in the database First, you need a dedicated identity for your Mastra a

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 a mastra sql server prevent delete rule by limiting database user rights. Give your AI agent read and write rights, then block row removal in the database. Many teams start by giving their agent a broad admin role during tests. If that role stays in place, one bad prompt can wipe out whole tables. Fixing roles early stops AI tools from deleting real data by mistake.

How to set up mastra sql server prevent delete in the database

First, you need a dedicated identity for your Mastra agent in the database. In SQL Server, a database user is the identity of a login for one database. If the agent needs many databases on an instance, use a login in master. If it only needs one database, you can use a contained database user. An admin must turn on contained databases on the server and database first.

Next, you assign roles to that user. SQL Server offers fixed database roles, and you cannot change their built-in permissions. Members of the db_datareader role can read all data from all user tables and views. If the agent only answers questions, this single role is enough. It stops all changes, including deletes, across the database.

Sometimes an AI agent must add new rows or update old ones. Teams often pick the db_datawriter role for this job. Still, db_datawriter lets a user add, delete, or change data in all user tables. It does not block delete commands on its own. Also, never use the db_owner role for an agent. That role can run all maintenance tasks and can even drop the whole database.

To allow inserts while blocking deletes, avoid broad fixed write roles. Instead, create a custom role for your agent user. Grant that role permission to read, insert, and update on the needed tables. Then, place an explicit deny rule on the delete action for those tables. In SQL Server, a deny rule overrides a grant rule. If you want to block all writes everywhere, use the db_denydatawriter role instead. Members of db_denydatawriter cannot add, change, or delete any data at all.

What native roles solve and what they miss

Database roles do a good job of setting hard walls at login time. When the Mastra agent logs in, SQL Server checks its user identity right away. If the agent tries to delete rows, the database rejects the command. This setup protects your tables from total loss during normal agent runs.

SQL Injection Prevention + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, database roles have big blind spots in daily operations. A role decides who logs in, not what a query does in flight. For example, an agent with update rights can still ruin a table. It can overwrite every row with blank values if a query lacks a filter. To the database, that bad update looks completely legal.

Native roles also struggle with sensitive data inside the tables you read. A member of db_datareader can read every column in every user table by default. If a table holds emails, phone numbers, card numbers, or national IDs, the agent gets them. Data masking that rewrites matched fields in results is a safer way to handle this. Finally, database roles cannot ask a human to approve one risky action on the fly. You either allow the action all the time or block it forever.

Add runtime guardrails beside SQL Server

Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.

With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.

Frequently asked questions

Can I edit the db_datawriter role to remove delete rights?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. You must make a custom role or apply a deny rule to the user.

What does the db_denydatawriter role do for an AI agent?

The db_denydatawriter role stops the user from adding, changing, or deleting any data in tables. Pair it with db_datareader when your agent only needs to read data.

Should my Mastra agent use a login in master or a contained user?

Use a contained database user if the agent only needs one or a few databases. Use a login in master if the agent needs many databases on the server.

The query runs. The emails stay hidden.