How to Configure JetBrains AI SQL Server Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up jetbrains ai sql server read only access with a dedicated database user. Then you assign that user to a read-only database role in your SQL Server instance. Many teams skip this step and reuse a broad developer login in their code editor. When an AI assistant uses an admin account, a bad query can change or delete data. Giving the tool a strict read-only role stops accidental writes before they happen. Setting up jetbrains ai sql server read only access with native roles SQL 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 jetbrains ai sql server read only access with a dedicated database user. Then you assign that user to a read-only database role in your SQL Server instance. Many teams skip this step and reuse a broad developer login in their code editor. When an AI assistant uses an admin account, a bad query can change or delete data. Giving the tool a strict read-only role stops accidental writes before they happen.

Setting up jetbrains ai sql server read only access with native roles

SQL Server separates server logins from database users to control who gets in. A database user is the identity of a login when it connects to a database. If a person needs to reach many databases, a standard login in master works well. For one or a few databases, you can use a contained database user with a password. An admin must enable contained databases on the server and the database first. SQL Server supports thirteen types of database users for different needs.

Once you create the user, you must limit what it can do inside the database. SQL Server includes fixed database roles that make this step fast for database admins. You cannot change the permissions assigned to these fixed database roles. Every role, including the public role, includes basic permission to connect to the database. To allow read queries, you add the user to the fixed data reader role. Members of this role can read all data from all user tables and views. You can also add the user to the deny data writer role for extra safety. Members of that role cannot add, change, or delete data in any user table.

Never put an AI tool account in the data writer or database owner roles. A data writer can add, delete, or change rows across all user tables. A database owner can run all maintenance tasks and even drop the whole database. After you set up the safe user, put those credentials in your editor data source. The AI assistant then uses that connection to inspect schemas and run safe select queries.

Why native roles are not enough for live queries

Fixed database roles do a great job of blocking write commands at the database level. Still, a read-only role only decides who logs in and which tables they can read. It does not inspect what individual statements do while they run in flight. Remember that the fixed data reader role reads all data from all user tables. If a table holds emails, phone numbers, card numbers, or national IDs, the query returns them.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

An AI assistant in a code editor often reads query results to help write code. This means raw customer data from user tables can flow straight into the model prompt. You can use the deny data reader role to block all user tables and views. Yet that blocks everything, unless you build custom roles and grant rights table by table. Maintaining custom roles across dozens of changing schemas takes a lot of manual work. Native roles also do not rewrite sensitive fields in results or require human approval per query.

Managing credentials across developer machines

Sharing a read-only password across a large engineering team creates another operational problem. Each developer has to store the database password inside their local code editor settings. When someone leaves the team, you must rotate that password and update every machine. Static database roles also apply the same broad read rules at all hours of the day. Teams often need a way to mask sensitive columns in flight without changing database schemas.

Add runtime guardrails beside SQL Server

Role grants and network rules are needed setup for SQL Server. Still, an allowed user or agent tool can send a bad delete or read raw customer fields over a valid session. In practice, hoop.dev runs beside the database as a sidecar for your SQL Server and checks TDS traffic without changing how clients log in.

With hoop.dev SQL Server controls, rules refuse banned statements before they run and can hold risky queries for human review. Matched sensitive values in supported result columns are masked before they reach the client. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for HIPAA, PCI DSS, SOX, and GDPR audits. See the source on GitHub.

Frequently asked questions

Can the fixed data reader role still read sensitive columns?

Yes, members of the fixed data reader role can read all data from all user tables. This includes columns with emails, phone numbers, card numbers, or national IDs unless you restrict them.

What is the difference between a standard login and a contained user?

A standard SQL user ties to a server login stored in the master database. A contained database user has its own password inside one database and needs server settings enabled.

How do I stop a database user from changing any table data?

Keep the user out of the data writer and database owner roles. You can also add the user to the deny data writer role to block all changes.

The query runs. The emails stay hidden.