How to Configure GitHub Copilot SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up github copilot sql server read only access with a dedicated database user. Then you assign that user to a fixed read-only database role in SQL Server. Many engineering teams start by sharing an existing admin login with their coding agent. That shortcut gives the agent power to change tables or drop the whole database. A scoped database user stops write queries before they run on your server. How github copilot sql server read only access works SQL Server splits access into server

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.

You set up github copilot sql server read only access with a dedicated database user. Then you assign that user to a fixed read-only database role in SQL Server. Many engineering teams start by sharing an existing admin login with their coding agent. That shortcut gives the agent power to change tables or drop the whole database. A scoped database user stops write queries before they run on your server.

How github copilot sql server read only access works

SQL Server splits access into server logins and database users. A login lets a person or tool enter the server instance. A database user is the identity of that login inside one specific database. Teams that need access to many databases often create a SQL user with a login. This login lives in the master database.

Teams that only need one or a few databases can use a contained user. A contained database user has a password and does not need a login in master. An admin must enable contained databases on the server and database first. SQL Server offers thirteen types of database users in total.

After you create the user, you assign it a fixed role. All database roles, including the public role, have basic permission to connect. The fixed reader role lets members read all data from all user tables and views. You can also add the deny writer role for extra safety. That role stops the user from adding, changing, or deleting any table data. Fixed role rules cannot be changed by admins. In contrast, the owner role can run all maintenance tasks and drop the database.

What native roles solve and what they miss

Native database roles do a great job of blocking changes at the table level. If GitHub Copilot tries to delete rows, SQL Server rejects the command right away. If it tries to alter a table schema, the database blocks that action too. This setup protects data integrity from bad agent prompts or mistakes.

Still, a fixed reader role still leaves major operational gaps open in practice. First, the fixed reader role grants read rights to every single user table and view. You cannot alter the rules inside a fixed database role. If a table holds emails, phone numbers, card numbers, or national IDs, the agent reads them. Those raw values then flow straight into the model prompt context.

Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Second, database roles only check who logs in and which tables they touch. They do not inspect what individual queries do while in flight. An agent with read rights can still run a massive join across big tables. That bad query can lock up memory and slow down your production app. Also, putting static database passwords on developer laptops creates a credential leak risk. Teams must rotate those shared passwords by hand whenever a person leaves.

How to scope table permissions for coding agents

To limit table exposure in native SQL Server, skip the fixed reader role. Instead, create a custom database role and grant read permission on safe tables only. You can also add the deny reader role to block access to sensitive views. Members of the deny reader role cannot read user tables, views, or their metadata.

Even with custom roles, safe tables often hold sensitive columns like emails or card numbers. Native SQL permissions take time to maintain across dozens of databases and developer machines. Teams still need a better way to handle credentials and rewrite sensitive results in flight.

Govern SQL Server queries and responses in flight

Table grants decide which SQL Server objects a caller may touch. They do not check query intent or rewrite sensitive values in results across every client and agent. By design, hoop.dev sits inline in the TDS wire protocol next to SQL Server as a sidecar for your SQL Server. On every production batch from an engineer or AI agent, hoop.dev decides to pass, mask, block, or hold it for approval.

With hoop.dev guardrails and hoop.dev data masking, banned statements are refused or held for review, matched sensitive fields in supported result columns are masked in memory, and each decision is logged. Check out the code on GitHub.

Frequently asked questions

Can I change the permissions inside a SQL Server fixed database role?

No, SQL Server does not let you change the permissions assigned to fixed database roles. If the fixed reader role is too broad, you must create a custom role instead.

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

Use a standard login in master if the agent needs access to many databases. Use a contained user with a password when it only needs one or a few databases.

Does read-only database access stop sensitive data from reaching the AI model?

No, a read-only role still lets queries return emails, phone numbers, and card numbers. You need dynamic data masking in flight to rewrite those matched fields in query results.

The query runs. The emails stay hidden.