How to Configure GitHub Copilot SQL Server Prevent Delete
To set up github copilot sql server prevent delete rules, assign read-only roles or deny permissions to the database user. You also need runtime checks so the coding agent cannot drop rows during active sessions. Many teams give their AI coding tools the same local database credentials that developers use every day. When an agent shares a broad role, one bad prompt can wipe out important tables in seconds. How to set up github copilot sql server prevent delete roles SQL Server controls what 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.
To set up github copilot sql server prevent delete rules, assign read-only roles or deny permissions to the database user. You also need runtime checks so the coding agent cannot drop rows during active sessions. Many teams give their AI coding tools the same local database credentials that developers use every day. When an agent shares a broad role, one bad prompt can wipe out important tables in seconds.
How to set up github copilot sql server prevent delete roles
SQL Server controls what a user can do through server logins and database users. A database user represents the identity of a login when it connects to a specific database. Admins can create a standard login in the master database for people who use many databases. They can also create a contained database user with its own password for a single database. Before doing that, an admin must enable contained databases on the server and the database.
Once the user exists, you control table actions through fixed database roles. The permissions inside fixed database roles are static and cannot be changed by admins. Adding a user to the data reader role lets them read all data from user tables and views. If the user is also in the data writer role, they can add, change, or delete rows. Even worse, the database owner role can run all maintenance tasks and drop the entire database.
To stop an AI agent from removing rows, you must strip away broad write roles. You can place the agent user in the data reader role alone for simple lookup tasks. You can also add the user to the deny data writer role to block all changes. That role stops the account from adding, changing, or deleting data across all user tables. If the agent still needs to insert or update rows, fixed roles will not be enough. In that case, an admin must deny the delete permission on specific tables or schemas.
What native database roles solve and what they miss
Native SQL Server roles do a good job of enforcing static rules at login time. If an account lacks delete rights, the database engine blocks any basic row removal statement right away. This setup works well when an AI agent only needs to read schema details or sample rows. It stops simple mistakes before they can harm your stored records.
Still, static database roles only decide who logs in and what broad actions they hold. They do not inspect what individual statements or commands try to do while in flight. Developers often run GitHub Copilot inside their own code editor using their personal database profile. If the developer needs write rights to build features, the AI agent inherits those exact same rights. Creating a separate read-only login for the tool often breaks normal coding tasks that need safe updates.
SQL Injection Prevention + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also fail to stop bad updates that act just like deletes. An agent with update rights can overwrite columns with null values across an entire table at once. Fixed roles cannot tell the difference between a safe single-row update and a table-wide wipe. Also, the data reader role lets the user read every column in every user table by default. That means raw query results can return emails, phone numbers, card numbers, and national IDs to the client.
Why runtime query checks matter for coding agents
Modern engineering teams need controls that look at each query right before it runs. Instead of relying only on broad login roles, teams should check the actual text of every statement. When you inspect traffic in flight, you can block delete and drop statements while allowing safe inserts. You can also require a human to approve risky changes before the database ever sees them.
Checking results in flight also helps teams protect private records during day-to-day coding work. Data masking rewrites matched fields like emails, phone numbers, card numbers, and national IDs in query results. This lets the coding agent see the shape of the table while keeping real user details safe. Teams get clear logs of every query the tool runs without managing dozens of custom database roles.
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 sits inline in the TDS wire protocol next to SQL Server as a sidecar for your SQL Server. On every production batch from an engineer or AI agent, hoop.dev decides to pass, mask, block, or hold it for approval.
With hoop.dev guardrails and hoop.dev data masking, banned statements are refused or held for review, matched sensitive fields in supported result columns are masked in memory, and each decision is logged. Check out the code on GitHub.
Frequently asked questions
Can fixed database roles block only delete statements in SQL Server?
No, the fixed deny data writer role blocks adding, changing, and deleting data all together. To block only deletes with native tools, an admin must set custom table or schema permissions.
Why is the database owner role risky for GitHub Copilot sessions?
Members of the database owner role can run any config or maintenance task on that database. An AI agent using that role can delete rows, drop tables, or even drop the whole database.
Does a read-only SQL Server role protect private user columns?
No, the fixed data reader role allows reading all data from every user table and view. You need data masking in flight to rewrite fields like emails, phone numbers, and card numbers in results.
Related guides
In this post