Guides

How to make AI coding agents safe on SQL Server

By Scott Coffey, Senior Database Developer. Updated 2026-10-15.

Cursor, Claude Code, and GitHub Copilot write T-SQL quickly. The risk is not that the code is bad on average; it is that a small share of it does something you would never approve if you read it carefully: a DELETE with no WHERE, a DROP that cannot be undone, a linked server mapped to sa, or a whole ETL design built on a tool you do not use. Safety comes from limiting what the agent can touch and changing what it writes by default.

1. Limit what the agent can reach

An agent that can run commands can run sqlcmd. Treat its connection like any other application login.

  • Point agents at a development or scratch instance, never production.
  • Use a dedicated login with only the rights the task needs. For diagnostics, that is usually VIEW SERVER STATE and VIEW DATABASE STATE.
  • Never give an agent a sysadmin login "to make it work".
CREATE LOGIN agent_readonly WITH PASSWORD = N'<set at run time>', CHECK_POLICY = ON;
GRANT VIEW SERVER STATE TO agent_readonly;
GRANT VIEW ANY DEFINITION TO agent_readonly;

USE SalesDev;
CREATE USER agent_readonly FOR LOGIN agent_readonly;
ALTER ROLE db_datareader ADD MEMBER agent_readonly;

In Claude Code, keep shell commands that touch databases on the "ask" list rather than auto-approved. In Cursor, leave auto-run off for terminal commands in database repos.

2. Write the rules down where the agent reads them

Each tool loads project instructions automatically: Cursor reads .cursor/rules/*.mdc, Claude Code reads CLAUDE.md, and Copilot reads .github/copilot-instructions.md. Put the same short rule set in each:

# SQL Server rules
- Automation = T-SQL stored procedures + SQL Server Agent + linked servers + PowerShell.
- Never generate SSIS packages. Offer T-SQL, Agent, or PowerShell instead.
- Start scripts with SET NOCOUNT ON; SET XACT_ABORT ON;
- Wrap writes in TRY/CATCH; CATCH does IF @@TRANCOUNT > 0 ROLLBACK; THROW;
- Scripts that change state expose @DryRun bit = 1 and PRINT intended actions.
- Batch large DML with DELETE TOP (@Batch) in short transactions.
- Never emit DROP, TRUNCATE, or DELETE without WHERE unless explicitly asked.
- No string-concatenated dynamic SQL; use sp_executesql and QUOTENAME.
- PowerShell: [CmdletBinding(SupportsShouldProcess)], no plaintext passwords.
- End every answer that contains a write with Rollback and Verify sections.

Short and specific rules work better than long essays. The agent follows "expose @DryRun bit = 1" far more reliably than "be careful with data".

3. Make every write a dry run first

The single most useful convention is that scripts which change state default to doing nothing. In T-SQL that is a @DryRun parameter that defaults to 1 and reports what would change:

IF @DryRun = 1
BEGIN
    SELECT COUNT_BIG(*) AS rows_that_would_change
    FROM dbo.Orders WHERE Status = N'Stale' AND UpdatedAt < @Cutoff;
    RETURN 0;
END;

In PowerShell it is SupportsShouldProcess, which gives every script a -WhatIf switch:

#Requires -Version 5.1
[CmdletBinding(SupportsShouldProcess)]
param([Parameter(Mandatory)][string]$ServerInstance, [string]$Database = 'master')
$ErrorActionPreference = 'Stop'
if ($PSCmdlet.ShouldProcess("$ServerInstance/$Database", 'Run maintenance script')) {
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -InputFile .\maint.sql
}

When the agent runs the script itself, it runs the safe path first, and you see the effect before anything changes.

4. Give the agent reference scripts, not just rules

Agents imitate what is in context. A tested template for a stored procedure, an Agent job, or a linked server gives the agent a concrete pattern to copy, including the error handling and idempotency checks that are easy to forget. Point the agent at the file by name in your prompt: "Follow proc-template-safe.sql."

5. Reject known-bad patterns on sight

Keep a list of output that should be rewritten, not reviewed line by line:

  • DELETE or UPDATE without a WHERE clause.
  • TRUNCATE TABLE in a cleanup script.
  • DROP without first scripting the object definition.
  • Enabling xp_cmdshell to call a batch file.
  • Hardcoded passwords in sp_addlinkedsrvlogin or PowerShell.
  • Adding a login to sysadmin, or EXECUTE AS LOGIN = 'sa'.
  • Recreating Agent jobs with sp_delete_job on every deploy, which loses history.
  • SSIS packages proposed for work that T-SQL and Agent already handle.

6. Review with a checklist before production

Before any agent-written change reaches production, confirm: it ran with the dry run first; the row counts match expectations; there is a rollback (a backup, an archive table, or a scripted inverse); it was tested on a copy with realistic volume; and the login it runs as has only the rights it needs. Put the checklist in the repo so the agent can fill it in as part of the pull request.

What this does not replace

These guardrails reduce the chance of a bad change and limit the damage of one. They do not replace backups, code review, or a test environment. An agent with a sysadmin connection to production can still do serious damage with perfectly formatted code, which is why step 1 comes first.