How to Configure JetBrains AI SQL Server Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up jetbrains ai sql server data masking by pairing restricted database roles with masking rules. This approach rewrites sensitive fields like emails and phone numbers in query results. Teams often connect their code editor to a test or live database with a shared login. When that login has broad permissions, the assistant and the user can read every column by default. How jetbrains ai sql server data masking works with database roles A database user is the identity of a login inside a

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 data masking by pairing restricted database roles with masking rules. This approach rewrites sensitive fields like emails and phone numbers in query results. Teams often connect their code editor to a test or live database with a shared login. When that login has broad permissions, the assistant and the user can read every column by default.

How jetbrains ai sql server data masking works with database roles

A database user is the identity of a login inside a single database. When someone needs to reach many databases on an instance, admins create a login in master. If the user only needs one or two databases, admins can use a contained database user. An admin must first turn on contained databases on the server and the target database. SQL Server supports thirteen types of database users for different login setups.

After creating the user, admins assign fixed database roles to control what the editor can read. Every role, including the public role, holds the basic permission to connect. Members of the db_datareader role can read all data from all user tables and views. You cannot change the permissions built into these fixed database roles. If a login gets the db_owner role, it can run all maintenance tasks and drop the database.

To limit what the assistant reads, admins set up column masking rules on specific tables. These rules replace raw values like card numbers and national IDs with masked text in results. If a user holds high privileges, the database returns the unmasked text instead. Keeping the editor login in a strict read role helps ensure the database rewrites matched fields.

What native roles solve and where gaps remain

Fixed database roles give teams a fast way to block writes or table drops. Adding a user to db_denydatawriter stops them from adding, changing, or deleting table rows. Adding them to db_denydatareader blocks them from reading any user tables, views, or table metadata. These roles decide who logs in and which broad actions they can take.

Still, static database roles have limits when developers use AI coding tools every day. A member of db_datareader can still query every column across every user table by default. If an admin forgets to add a masking rule to a new column, raw data returns. The database role only checks who logs in at the start of the session.

AI Data Exfiltration Prevention + Data Masking (Static): architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native roles do not inspect individual queries in flight to check what the prompt asked. They also require admins to update rules on every database instance by hand. When teams manage dozens of databases, column rules often drift out of sync. A single missed table can send raw emails or card numbers back to the editor.

Managing read permissions across multiple database instances

Most engineering teams work across many staging and production databases at the same time. Maintaining separate users and column rules on each server takes constant manual work. Admins must check every schema change to see if new columns hold phone numbers or emails. If a developer switches to a shared admin account to debug an issue, masking stops working.

Teams need a way to enforce masking rules without rebuilding roles on every server. Separating the user identity from raw database credentials helps keep access rules consistent. When rules apply to the query results in flight, admins avoid schema drift across instances. This keeps sensitive fields rewritten in results even when schemas change fast.

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

Does the db_datareader role hide sensitive columns by default?

No, members of db_datareader can read all data from all user tables and views. You must add separate masking rules to rewrite emails or card numbers in results.

Can I customize the permissions inside SQL Server fixed database roles?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. To restrict specific tables, you must create custom roles or use deny roles.

When should I use a contained database user for an editor connection?

Use a contained database user when the person only needs one or a few databases. An admin must first enable contained databases on the server and the database.

The query runs. The emails stay hidden.