How to Configure Amazon Q SQL Server Read Only Access
You set up amazon q sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role before the AI tool connects. Many engineering teams start by sharing their own local database credentials with their coding assistant. If those accounts hold broad rights, the AI assistant can change or delete production data. How to set up amazon q sql server read only access SQL Server manages permissions through logins at the instance level and user
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 amazon q sql server read only access by creating a restricted database user. Then you assign that user to a read-only database role before the AI tool connects. Many engineering teams start by sharing their own local database credentials with their coding assistant. If those accounts hold broad rights, the AI assistant can change or delete production data.
How to set up amazon q sql server read only access
SQL Server manages permissions through logins at the instance level and users inside each database. A database user serves as the identity of a login when it connects to a database. If your AI assistant needs to query many databases, you create a login in the master database. Then you map a database user to that login inside each target database.
For access to one or a few databases, you can use a contained database user instead. This user has a password and is not tied to a login in the master database. An admin must first enable contained databases on the server and on the specific database. SQL Server supports thirteen different types of database users for various login needs.
Once the user exists, you grant read permissions through fixed database roles. Adding the user to the datareader role lets it read all data from all user tables. This role also lets the user read from all views in that database. You can also add the user to the denydatawriter role as an extra safeguard. Members of that role cannot add, change, or delete data in any user tables. The permissions assigned to these fixed database roles cannot be changed by admins.
How Amazon Q reaches your database
Amazon Q Developer assists engineers inside their code editors and from the command line. When a developer asks a question about data, the agent can run local commands or tools. It uses the local shell or configured tool extensions to connect to external systems.
To reach SQL Server, the assistant uses the database credentials stored on the developer machine. You place the read-only user credentials into the local connection settings that the agent reads. Every query the assistant generates then runs under the database user you created. If the assistant tries to update a table, SQL Server blocks the statement right away.
Auditor Read-Only Access + Kubernetes API Server Access: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native read-only roles solve and what they miss
Fixed database roles do a good job of stopping accidental writes and table drops. Unlike the owner role, which can drop the database, a read-only role protects data integrity. The database engine enforces this rule at login time and checks every write attempt.
Still, native database roles only decide who logs in and which tables they can read. They do not inspect what individual queries do in flight or what data comes back. A member of the fixed datareader role can read every column in every user table by default. If a table holds emails, phone numbers, card numbers, or national IDs, the query returns them. Those raw values then flow straight into the AI assistant context window and model prompts.
Native roles also force you to distribute static passwords or connection strings to developer laptops. If an engineer works across several databases, managing those local secrets becomes hard to track. Also, standard database logs only show that the shared read-only user ran a query. They do not tie each query back to the specific developer who prompted the AI assistant. Teams often want field-level masking that rewrites matched fields in results before the tool processes them.
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 is an open-source proxy that sits next to SQL Server as a sidecar for your SQL Server. It inspects TDS batches and parameterized queries in real time.
With hoop.dev guardrails and hoop.dev data masking, banned statements are refused with your message. Matched sensitive fields in supported result columns are masked in memory, and each decision is logged per listener. Check out the code on GitHub.
Frequently asked questions
Can I change the permissions of the fixed datareader role in SQL Server?
No, SQL Server does not let you change the permissions assigned to fixed database roles. If you need custom table rules, you must create a custom database role and grant select permissions per table.
Should I use a standard login or a contained user for an AI coding assistant?
Use a contained database user if the assistant only needs to read from one or a few databases. Use a login in the master database if the assistant must query many databases across the same instance.
Does the datareader role hide sensitive columns like emails or card numbers?
No, the fixed datareader role grants read access to all data in all user tables and views. To hide specific fields like phone numbers or national IDs, you need column-level rules or result masking.
Related guides
In this post