How to Configure GitHub Copilot SQL Server Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up github copilot sql server least privilege, create a read-only database user. Then bind your AI tool to that restricted account instead of an admin login. Many teams give coding assistants shared admin keys during setup to save time. That broad role lets the AI agent change tables, drop objects, or read sensitive rows. A tight permission model stops bad queries before they harm your database. How to set up github copilot sql server least privilege SQL Server separates server logins f

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 least privilege, create a read-only database user. Then bind your AI tool to that restricted account instead of an admin login. Many teams give coding assistants shared admin keys during setup to save time. That broad role lets the AI agent change tables, drop objects, or read sensitive rows. A tight permission model stops bad queries before they harm your database.

How to set up github copilot sql server least privilege

SQL Server separates server logins from database users. A login lives in the master database and lets a person or tool enter the server. A database user is the identity of that login inside one specific database. When an AI coding agent only needs one database, you can use a contained user. A contained database user has its own password and does not need a master login. An admin must enable contained databases on the server and the target database first.

Next, you assign permissions to that user. SQL Server offers fixed database roles with permissions that cannot be changed. Do not add the AI account to the owner role. Members of that owner role can change settings and even drop the database. Instead, add the account to the data reader role. This role lets the user read all data from all user tables and views. You can also add the account to the deny data writer role. That role makes sure the user cannot add, change, or delete any table data.

Why native roles are faster to outgrow with AI

Fixed database roles work well for basic access control. They decide who can log in and which broad actions they can take. Still, the data reader role still reads every user table and view in the database. If you want to hide specific tables, you must skip the fixed reader role. You have to grant read rights table by table or build custom views.

Even with custom grants, native roles only check permissions when a query starts. They do not inspect what individual statements do in flight. A read-only user can still pull thousands of customer emails, phone numbers, or card numbers. An AI agent can read those values and send them out in its context window. Static database roles also require you to store a database password on the developer machine. Anyone with access to that local file can use the credentials outside the coding tool.

Least Privilege Principle + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to reduce risk on developer laptops

Teams should separate human logins from AI tool accounts. Give the AI assistant its own database user so your database logs show its queries. Keep the account out of writer roles in every database. Review the tables in your schema before you grant the fixed reader role. If a table holds national IDs or card numbers, block access to that table. You can use the deny data reader role or table-level denies to block those reads. Finally, rotate the password for the AI database user on a regular schedule.

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 an AI tool?

Use a contained database user if the AI tool only needs one or two databases. It keeps the account inside that single database without a login in the master database.

Does the fixed data reader role hide sensitive columns?

No, the fixed data reader role allows reads on all user tables and views. To hide specific columns, you must grant permissions on individual columns or views instead.

Can a read-only SQL Server role stop an AI tool from reading PII?

A read-only role stops data changes, but it does not rewrite query results. If the user has read rights on a table, the AI tool receives the raw fields.

The query runs. The emails stay hidden.