How to Configure Azure SQL Slow Query Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up the azure sql slow query log by turning on query tracking. Then you send those performance events to a storage workspace for review. Teams often grant broad database roles so engineers can inspect slow statements. Giving out wide permissions creates a major risk during routine performance checks. Engineers who only need to read query data often get full admin rights. How the azure sql slow query log and roles work To check slow queries, an engineer must first log in to the database

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 the azure sql slow query log by turning on query tracking. Then you send those performance events to a storage workspace for review. Teams often grant broad database roles so engineers can inspect slow statements. Giving out wide permissions creates a major risk during routine performance checks. Engineers who only need to read query data often get full admin rights.

How the azure sql slow query log and roles work

To check slow queries, an engineer must first log in to the database. A database user is the identity of a login inside a single database. If a person checks many databases, teams create a user tied to a master login. If a person needs just one database, teams can use a contained user. An admin must turn on the contained setting on the server and database first. Microsoft supports 13 types of database users for these different login needs.

Once inside, the user needs permissions to read the slow query data and tables. Teams often assign fixed database roles to manage what a user can do. You cannot change the permissions that come built into these fixed database roles. Every role, including the public role, lets the user connect to the database. To keep things easy, admins often put engineers into the full owner role. A user in the owner role can run all setup and maintenance tasks. That same user can even drop the entire database by mistake.

Some teams try to limit risk by using the fixed data reader role instead. Members of this reader role can read all data from all user tables. They can also read every view inside the database. If you want to block table reads, you can assign the deny reader role. Users in that role cannot read user tables, views, or metadata about them. You can also combine roles with the data writer or deny writer roles. Members of the data writer role can add, delete, or change table data. Members of the deny writer role cannot add, change, or delete any data.

What native roles solve and what they miss

Native database roles do a good job of controlling who can log in. They also set baseline rules for whether a user can read or write tables. Using a contained user stops an engineer from jumping across every database you run. Putting someone in a deny writer role stops them from deleting production rows. These native controls form a strong first step for database security.

Still, native roles leave a big gap when engineers debug slow queries. To test a slow query, an engineer usually needs to run it again. If you give them the standard reader role, they can read every user table. That means query results will return live emails, phone numbers, and card numbers. The native role checks who logs in, not what each statement returns in flight.

SQL Query Filtering + Azure RBAC: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Logging slow queries also does not stop a bad query before it runs. The log only tells you about a heavy or risky query after it finishes. If an engineer has owner rights to view logs, they can run destructive commands. Native roles cannot pause a dangerous statement for peer approval before it runs. They also cannot mask national IDs or card numbers in the query output.

How to tighten access during query tuning

Start by removing engineers from the database owner role during daily work. Give each person the narrowest user type that fits their job. Use contained database users when a person only tunes a single database. Pair the deny data writer role with your tuning accounts to block accidental changes.

Next, separate the right to view query logs from the right to read tables. Engineers can find slow queries in your log workspace without touching live customer data. When they must run a test query on live tables, check the statement first. Adding statement-level controls lets you rewrite sensitive fields like emails in the results.

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 sits inline in the TDS protocol beside the database as a sidecar for your SQL Server. For every query an agent or human runs in production, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev SQL Server controls, rules refuse banned statements before they run, hold risky queries for human review, and mask matched sensitive values in supported result columns in memory. 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 I change the permissions inside a fixed database role?

No, you cannot change the permissions assigned to fixed database roles in SQL Server. If a fixed role is too broad, you must combine roles or build custom rules.

What is the risk of using the data reader role for query tuning?

Members of the data reader role can read all data from all user tables. When engineers test slow queries, they will see raw emails, phone numbers, and card numbers.

Should I use a master login or a contained user for log review?

Use a login in master if the engineer needs access to many databases. Use a contained database user if they only need access to one or a few databases.

The query runs. The emails stay hidden.