GitHub Copilot Agent Mode Comes to SSMS
Microsoft just shipped a public preview of Agent mode for GitHub Copilot in SQL Server Management Studio. This isn’t the Copilot you already know from VS Code. Instead of inline completions or chat responses, Agent mode can execute multi-step database tasks autonomously – running queries, analyzing execution plans, and proposing schema changes, all with your approval at each step (source).
For DBAs and SQL Server developers, this is the first time an AI assistant has been able to actually do things inside SSMS, not just suggest text you then copy-paste into a query window.
What Agent Mode Actually Does
Agent mode works as an autonomous co-pilot for your database. You give it a high-level goal like “find slow queries in AdventureWorks and suggest indexes” and it works through the problem step by step. Microsoft’s documentation shows it can investigate performance problems, tune queries, review maintenance and configuration, identify security concerns, and troubleshoot errors.
The key difference from standard Copilot chat: Agent mode can execute T-SQL against your database, read results, and use those results to decide what to do next. It’s iterative, not one-shot.
Each action requires explicit approval before execution. You can approve individual steps, approve for the current session, or always trust a particular tool. This granular permission model means you stay in control while the agent does the grunt work (source).
Setting Up Agent Mode
You need SSMS 22.7 or later with the “AI Assistance for SSMS” workload installed. The setup is straightforward:
- Open SSMS and connect to your SQL Server or Azure SQL database
- Open GitHub Copilot Chat from the View menu
- Switch from Chat mode to Agent mode using the dropdown
- Start with a targeted goal like “Find the top 5 most expensive queries in this database”
Agent mode operates under your existing SQL Server login credentials. It cannot access databases you don’t have permissions on, and every query it runs is logged in SQL Server’s regular audit trail (source).
Execution Context and the Constitution File
One of the more interesting design decisions is the execution context model. You can configure Agent mode to run under a specific database user rather than your own login by defining a CONSTITUTION.md file. This file lives in your database’s documentation and specifies which SQL user the agent should impersonate (source).
This is the right approach for production environments. Create a dedicated low-privilege user with only the permissions Agent mode needs (likely SELECT on specific tables and EXECUTE on specific stored procedures), then point the constitution file at that user. The agent literally cannot exceed those permissions because SQL Server enforces them, not Copilot.
As Microsoft notes: “Copilot’s approval system is not a security boundary. The actual security boundary is SQL Server’s permission enforcement.” That’s an important distinction. The approval dialog is a convenience. The database permissions are the real defense (source).
MCP Server Integration
Agent mode also supports the Model Context Protocol (MCP), which means it can connect to external tools and data sources beyond what SSMS exposes natively. For performance tuning, it can pull data from the Query Store. For security reviews, it can check error logs and configuration (source).
This is where things get interesting. An agent that can query your Query Store, correlate slow queries with index usage, and propose specific index changes is more useful than one that just guesses based on training data. The MCP integrations give it real, current data about your actual database workload.
Practical Use Cases
- Query tuning: “Find queries with high logical reads and suggest indexing strategies.” Agent mode pulls actual plans from the plan cache, identifies table scans, and proposes specific indexes.
- Security review: “Check for users with sysadmin privileges who shouldn’t have them.” Agent mode queries server roles and cross-references with your documented access policies.
- Maintenance: “Review index fragmentation and rebuild indexes over 30% fragmented.” Agent mode checks fragmentation levels and generates ALTER INDEX statements.
- Troubleshooting: “Look at the last 24 hours of error logs and summarize critical warnings.” Agent mode reads the error log and categorizes issues by severity.
Bottom Line
Agent mode in SSMS is a genuine step forward for database tooling. It moves Copilot from a passive suggestion engine to an active participant in database administration. The design choices around execution context and permission enforcement show Microsoft thought through the security implications instead of just bolting on an AI chat window.
If you manage SQL Server databases, install SSMS 22.7 and try Agent mode on a dev instance this week. Start small – ask it to analyze one query rather than refactor your whole schema. The approval workflow means you can’t accidentally cause damage, and the MCP integration means it can actually see what’s happening in your database instead of guessing.