Every developer building an MCP server for SQL Server hits the same fork in the road within the first five minutes.
Do you let the AI talk directly to your tables? Or do you route everything through stored procedures?
It sounds like a small decision. It’s not. This single choice shapes the security of your data, the flexibility of your AI assistant, and how much you’ll trust the whole system six months from now.
I’ve seen teams go both ways. I’ve seen both blow up. Here’s what I think after watching this play out across different projects.
The Direct Table Approach: Powerful and Terrifying
The idea is simple. You give the AI tools to explore your database schema and then a tool to run SQL queries. The AI looks at the structure, understands relationships, writes SQL on the fly, and returns an answer.
User: “Show me the top 10 customers by revenue last quarter.”
The AI figures out joins, writes the GROUP BY, applies date filters, and returns a clean result — without anyone pre-building that report.
This is where direct table access shines — ad-hoc exploration. Questions that change every day.
The AI is writing SQL against your production database. It doesn’t understand table size, indexing strategy, locking behavior, or sensitive schemas like
hr.employees.
You can add guardrails. Whitelist SELECT statements. Block dangerous keywords. Add automatic row limits. Use a read-only database user.
Every guardrail is a patch over a deeper issue — you’re giving an unpredictable system access to a structured one.
There’s also an observability problem. A stored procedure has a name. An AI-generated query is just a string. Logging helps — but you lose design intent.
Direct table access is powerful for internal analytics and exploration — but risky for customer-facing systems or sensitive environments.
The Stored Procedure Approach: Safe and Rigid
Instead of letting the AI write SQL, you pre-build stored procedures and expose them as MCP tools.
GetCustomerByIdCreateOrderUpdateShipmentStatus
The AI can call these — but nothing else.
The AI becomes the trigger. The logic stays inside reviewed, versioned, tested stored procedures.
Validation, permission checks, and audit logging all live inside the SP — exactly where they should.
Stored procedures remove guesswork. The AI knows exactly what parameters are required and what the operation will return.
Direct tables = open kitchen.
Stored procedures = structured menu.
But rigidity is real. If no SP exists for a question, the AI is stuck. Now you’re back in the ticket cycle.
If your SP library isn’t flexible, your AI becomes limited instead of powerful.
Stored procedures are mandatory for write operations and sensitive logic — but too restrictive as your only access layer.
The Hybrid: Where I’ve Landed
After going back and forth, here’s what works best for most teams:
- Read → Direct table access (SELECT-only, heavily guarded)
- Write → Stored procedures only
- Schema discovery → Always enabled
The AI can read any report in the building.
But if it wants to change anything, it must go through proper forms and approvals.
The Steps to Get There
Step 1: Start with Schema Discovery
Expose table listings, column definitions, primary keys, and relationships.
An AI that understands your schema writes dramatically better queries.
Step 2: Add Read-Only Direct Queries
Create a dedicated SELECT-only database user. Never use sa. Never reuse an existing login.
This is the single most important security decision in your MCP architecture.
Add keyword blocking, row limits, and timeouts. Then try to break it yourself.
Step 3: Build Your SP Layer
You need:
- SP discovery
- SP parameter metadata
- Controlled SP execution
Expose only what makes sense for an AI assistant. Don’t expose everything.
Step 4: Add Prompts for Common Workflows
Guide the AI to explore schema first, then execute. Guide it to discover SP parameters before calling them.
These prompts are training wheels — they encode best practices.
Step 5: Test With Real Scenarios
Don’t test with toy examples. Use real business questions.
• Why did revenue drop last Tuesday?
• Show customers inactive for 90 days but active last quarter.
• Create an order using items from the last purchase.
Hard-Won Lessons
Set it up from day one. It takes five minutes and protects everything.
Every query. Every SP call. Every error.
Expose 5–10 procedures first. Expand based on usage patterns.
CREATE, ALTER, DROP do not belong in most MCP implementations.
Where This Is Heading
We’re building a natural language layer on top of structured databases.
Get it wrong and your AI is either too dangerous or too limited.
Direct-only → Risky
SP-only → Rigid
Hybrid → Flexible + Safe + Auditable
That’s worth building.
In the next article, we’ll extend this pattern — adding multiple databases, connecting MCP servers, and building an AI assistant across your data infrastructure.