Introducing Execution Context for GitHub Copilot in SSMS

The GA release of GitHub Copilot in SSMS in March included Ask mode support for the chat window, interaction with the results pane, and code completions.  Yesterday’s release of SSMS 22.7 introduced Agent mode (preview) for the chat window, backed by skills to complete complex, multi-step tasks on your behalf.  You've been able to ask questions in natural language, get help writing T-SQL, and interact with your database right from SSMS. Now you can leverage built-in skills to get help troubleshooting performance issues, analyze missing indexes, review configuration and more, as well as create custom skills to streamline your own workflows.

But...since the very first time I presented about Copilot, I’ve been asked: can we control what Copilot is allowed to do, separately from the user's own permissions? When Copilot executes queries, it uses the same credentials as the connected user. If you connect as a sysadmin, Copilot has sysadmin-level access. Customers asked for a way to let users take advantage of GitHub Copilot, but limit what queries it can execute.

Introducing agentExecuteAsUser in the database CONSTITUTION.md

Starting with SSMS 22.7, database owners can now configure the Copilot execution context separate from the user's permissions.  You can specify either a database user or SQL login that Copilot uses when running queries.  This gives organizations the control they've been asking for: users keep their access, but Copilot is constrained to exactly what you allow.  The configuration is stored in the frontmatter of the database's CONSTITUTION.md file (database-level extended property), and must be configured per database.  The following format must exist in the CONSTITUTION.md:

---
agentExecuteAsUser: GHCP_DatabaseUser
---

How to set it up

The following T-SQL code shows how to configure a database user, GHCP_DatabaseUser, for the General Hospital database.

Step 1: Created a dedicated database user (or SQL login) with limited permissions

Follow the principle of least privilege when creating the user or login you create for GitHub Copilot to use.

/* Set database context */

USE GeneralHospital;

/* Add database user with READONLY permissions, except on dbo.CustomerPayments */

DECLARE @GHCPUser NVARCHAR(128) = N'GHCP_DatabaseUser'; 

/* Create a contained database user (no server login required) */ 

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @GHCPUser) 

BEGIN 

    DECLARE @sql NVARCHAR(MAX) = N'CREATE USER ' + QUOTENAME(@GHCPUser) + N' WITHOUT LOGIN'; 

    EXEC sp_executesql @sql; PRINT 'Created user: ' + @GHCPUser; 

END 

    ELSE PRINT 'User already exists: ' + @GHCPUser; 

/* Grant the user appropriate permissions (adjust to your scenario) db_datareader is a safe starting point, add additional roles or specific GRANTs as needed */ 

DECLARE @roleSql NVARCHAR(MAX) = N'ALTER ROLE db_datareader ADD MEMBER ' + QUOTENAME(@DBUser); 

EXEC sp_executesql @roleSql; 

/* Deny agent user READ access to a table with sensitive data */ 

DECLARE @denySql NVARCHAR(MAX) = N'DENY SELECT ON dbo.CustomerPayments TO ' + QUOTENAME(@DBUser); EXEC sp_executesql @denySql;

Step 2: Add the CONSTITUTION.md extended property for the database

Specify the user in the frontmatter of the CONSITUTION.md for the database, which is stored as a database-level extended property.  You can add or update it with T-SQL.

/* Set database context */

USE GeneralHospital;

/*

Check to see if CONSITUTION exists

*/

SELECT

name,

CAST(value AS NVARCHAR(MAX)) AS ConstitutionContent

FROM sys.extended_properties

WHERE class = 0 AND name = N'CONSTITUTION.md';

/*

Write (or update) CONSTITUTION.md with the agentExecuteAsUser frontmatter

*/

DECLARE @constitution NVARCHAR(4000) =

    N'---' + CHAR(10)

   + N'agentExecuteAsUser: ' + @DBUser + CHAR(10)

   + N'---' + CHAR(10)

   + N'# Database Constitution' + CHAR(10)

   + CHAR(10)

   + N'## Purpose

This database manages operations for General Hospital including patient records, appointments, medical staff, and billing information.

## Naming Conventions

- **Tables**: Use abbreviated names for clarity (e.g., Pts = Patients, Docs = Doctors, Apts = Appointments)

- **Primary Keys**: Use `[TableName]ID` format

- **Foreign Keys**: Use descriptive names indicating the relationship



## Data Integrity Rules

- All tables must have a primary key

- Use foreign key constraints to enforce referential integrity

- Implement appropriate indexes on foreign key columns

- Use constraints to enforce business rules at the database level



## Coding Guidelines

- Avoid SELECT * in production code

- Use appropriate data types (avoid oversizing)';



IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE class = 0 AND name = N'CONSTITUTION.md')

    EXEC sp_updateextendedproperty @name = N'CONSTITUTION.md', @value = @constitution;

ELSE

    EXEC sp_addextendedproperty @name = N'CONSTITUTION.md', @value = @constitution;



/* Verify the CONTITUTION.md extended property was created/updated correctly */

PRINT 'CONSTITUTION.md written with agentExecuteAsUser: ' + @DBUser;

Step 3: Grant IMPERSONATE permission to users of GitHub Copilot

Any SSMS user that will use GitHub Copilot for the database must have the IMPERSONATE permissions when an agentExecuteAsUser is specified.  Use of IMPERSONATE is intentional — it ensures that the database owner explicitly controls which users can leverage Copilot in a database with a configured execution context. If a user doesn't have IMPERSONATE permission, they can't use GitHub Copilot in SSMS against that database.

USE GeneralHospital;

DECLARE @DBUser NVARCHAR(128) = N'GHCP_DatabaseUser';

DECLARE @RegularUser NVARCHAR(128) = N'PeterParker';

/*

Grant IMPERSONATE to the users (or to a role) of GitHub Copilot

This must be granted to the principal that the copilot actually connects as.

*/

DECLARE @impersonateSql NVARCHAR(MAX) =

N'GRANT IMPERSONATE ON USER::' + QUOTENAME(@DBUser) + N' TO ' + @RegularUser;

EXEC sp_executesql @impersonateSql;

As a general best practice, apply the principle of least privilege to all database users.  This also applies to the user you configure for agentExecuteAsUser. Grant only the minimum permissions needed for the tasks you want Copilot to assist with. If Copilot only needs to execute with SELECT queries, don't grant INSERT or UPDATE. If it doesn't need access to certain schemas or tables, don't grant it. The tighter the permissions on this user, the more confidence you have in what GitHub Copilot in SSMS can and can't do.

Step 4: Verify the configuration

Once configured, GitHub Copilot in SSMS will automatically impersonate the specified user when generating and executing queries. You can verify this by asking Copilot to run a query that accesses a denied table.  Access will fail, confirming the security boundary is in place.

What this means in practice

Consider a Data Engineer who has db_owner on a production database - perhaps they need it for deployments and maintenance. Without a configured execution context, GitHub Copilot executes queries with db_owner permissions. With agentExecuteAsUser, you can give Copilot read-only access while the engineer retains full permissions for their day-to-day work and in the Query Editor.

The user's permissions and capabilities, outside of GitHub Copilot, are unchanged. They can still run any query they have permissions to execute in the Query Editor, they can still navigate within Object Explorer. The Copilot execution context only applies to what GitHub Copilot in SSMS executes on their behalf.

Combining with CONSTITUTION.md guidelines

The agentExecuteAsUser setting works alongside the guidelines you define in the CONSTITUTION.md body. While agentExecuteAsUser follows permissions established at the database engine level, the rules in the constitution body provide behavioral guidelines for the AI model.

Together, they provide:

• Hard boundary: Database permissions for agentExecuteAsUser prevent unauthorized data access
• Soft boundary: CONSTITUTION guidelines direct Copilot's behavior (e.g., “never suggest DROP statements,” “always include WHERE clauses on DELETE”)

Get started

To leverage execution context for GitHub Copilot in SSMS, update to version 22.7 and configure the agentExecuteAsUser frontmatter in your database constitution.  We've had a delay in getting documentation for the release published (apologies!), but once that is resolved, you can check out the docs. In the interim, check out the quick video on our YouTube playlist, along with all the others videos for GitHub Copilot in SSMS (including Agent mode!).  As always, please share your feedback using the Send Feedback button in SSMS or on the SSMS feedback site.