How to Configure GitHub Copilot SQL Server Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up github copilot sql server permissions, create a dedicated read-only database user. Then assign that user only the table roles needed for coding tasks. Many teams start by giving an AI assistant their own local database credentials. When roles are too broad, a single bad query can change or drop tables. Limiting what the assistant can read and write stops accidental data loss early. How github copilot sql server permissions work GitHub Copilot reaches databases through local tools or

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 permissions, create a dedicated read-only database user. Then assign that user only the table roles needed for coding tasks. Many teams start by giving an AI assistant their own local database credentials. When roles are too broad, a single bad query can change or drop tables. Limiting what the assistant can read and write stops accidental data loss early.

How github copilot sql server permissions work

GitHub Copilot reaches databases through local tools or Model Context Protocol servers in your editor. Those tools use whatever database user and password you put in your local settings. Every query the assistant runs uses the identity of that specific SQL Server user. SQL Server checks permissions at two levels before it runs any query. First, a login lets the user enter the server instance through the master database. Second, a database user maps that login to a specific database on the server.

SQL Server supports thirteen different types of database users for different access needs. When a user needs access to many databases, admins create a login in master. When an assistant only needs one database, a contained database user works well. A contained user has a password stored inside the target database itself. It does not tie back to a server login in the master database. Before using contained users, an admin must turn on contained databases on the server. The admin must also turn on that setting on the target database.

Using fixed roles to limit database risk

Once the user exists, you must decide what actions it can take. Every database user starts in the public role, which grants basic connection rights. SQL Server includes fixed database roles with preset rules that admins cannot change. Give the assistant the fixed data reader role when it only inspects schemas. Members of the data reader role can read all data from all user tables. They can also read data from all user views in that database.

You should pair that role with the fixed deny data writer role for safety. Members of the deny data writer role cannot add, change, or delete any data. Avoid giving an AI assistant the data writer role in shared or live databases. Never assign the database owner role to a coding tool or helper. A database owner can change all settings, run maintenance, and drop the whole database. If a table holds secret records, use the deny data reader role or custom grants. A user in the deny data reader role cannot read any user tables or views.

Kubernetes API Server Access + GitHub Actions Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native database roles fall short for AI tools

Native SQL Server roles decide who can log in and which tables they see. They do not inspect what individual statements or queries do while in flight. A read-only user with the data reader role can still read every user table. That means an AI assistant can pull customer emails, phone numbers, and card numbers. Those sensitive fields then flow straight into the model prompt and external servers.

Static database credentials also create a tracking problem for security and platform teams. When ten developers share one read-only login, server logs show only that single login. You cannot tell which developer or coding agent ran an expensive query. Also, developers must store the database password on their local machines. A local script or agent tool could read that password file by mistake. Native roles are a good first step, but they do not filter results dynamically.

Check SQL Server batches and results on the wire

SQL Server logins and roles decide who connects. They do not check what each TDS batch does or mask sensitive values in returned rows. With hoop.dev, you run an open-source proxy, a sidecar for your SQL Server, inline in the TDS wire protocol. On every batch an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Before a batch hits the server, hoop.dev guardrails refuse disallowed actions with a native TDS error or hold risky statements for approval. On the way back, hoop.dev data masking rewrites sensitive values in supported result columns in memory, and every decision is logged. Explore the MIT-licensed project on GitHub.

Frequently asked questions

Should I use a server login or a contained user for GitHub Copilot?

Use a contained database user if the assistant only needs one or two databases. This keeps the account inside the target database and avoids master server logins.

Does the SQL Server data reader role hide sensitive columns from AI prompts?

No, the fixed data reader role lets the user read all user tables and views. It does not rewrite emails, phone numbers, or national IDs in query results.

Why should I avoid the database owner role for local coding assistants?

The database owner role allows all maintenance tasks and can drop the entire database. An AI agent with this role could wipe out tables with one bad query.

The query runs. The emails stay hidden.