How to Configure Azure SQL Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up an azure sql audit log by turning on auditing at the server or database level and choosing a log storage target. The database engine then records login events, role changes, and data queries to your storage location. Teams often pair this log with built-in database roles to track what users do. Still, broad roles still let users read or change too much data before the log ever records the event. How the azure sql audit log and native roles work To understand what appears in your az

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 an azure sql audit log by turning on auditing at the server or database level and choosing a log storage target. The database engine then records login events, role changes, and data queries to your storage location. Teams often pair this log with built-in database roles to track what users do. Still, broad roles still let users read or change too much data before the log ever records the event.

How the azure sql audit log and native roles work

To understand what appears in your azure sql audit log, you must first look at how users log in. A database user is the identity of a login when it connects to a database. When a person needs to reach many databases on an instance, admins create a SQL user tied to a login in the master database. If a person only needs one or a few databases, a contained database user with a password is an option. An admin must first turn on contained databases on the server and the database. Across the platform, there are thirteen types of database users.

Once a user logs in, fixed database roles control what they can do. You cannot change the permissions assigned to these fixed roles. Every role, including the public role, has basic permission to connect. Admins add users to specific roles to grant access to tables. Members of the data reader role can read all data from all user tables and views. Members of the data writer role can add, delete, or change data in all user tables. Meanwhile, the database owner role can run all maintenance tasks and even drop the database.

Admins can also use deny roles to block access. The deny data reader role stops a user from reading any data or metadata from user tables and views. The deny data writer role stops a user from adding, changing, or deleting data. As users with these roles run queries, the audit feature writes their actions to your chosen log destination.

What native auditing solves and what it misses

Native auditing gives security teams a historical record of database events. You can review the logs later to see which login connected and which queries ran. This helps teams pass security reviews and investigate past incidents.

Still, native roles and logs leave a major operational gap. A database role decides who can log in and which tables they can reach. It does not inspect what individual statements do in flight. For example, a support engineer in the data reader role can read every row in every user table by default. If a table holds emails, phone numbers, card numbers, or national IDs, that user can query all of them. The audit log only tells you about the query after the data has already left the database.

Audit Log Integrity + Azure RBAC: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Why shared logins weaken your audit trail

Audit logs also lose value when teams share database logins. In many setups, several engineers use the same SQL user and password in their desktop tools. When someone runs a bad delete statement, the log only shows the shared SQL login name. You cannot tell which actual person on the team ran the command.

Even when each engineer has a personal login, managing roles across dozens of databases takes heavy manual work. Admins often grant the database owner or data writer role just to unblock a ticket fast. Those broad permissions stay active long after the ticket closes. Native logs will record future queries from that account. However, they cannot stop a risky command or mask sensitive columns during a live session.

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 you change the permissions of fixed database roles in SQL Server?

No, you cannot change the permissions assigned to fixed database roles. If the built-in data reader or data writer roles are too broad, you must create custom roles or control queries before they hit the database.

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

A standard SQL user connects through a login stored in the master database, which works well for reaching many databases. A contained database user has its own password inside a single database and does not rely on a master login.

Does the data reader role hide sensitive columns from audit and query results?

No, members of the data reader role can read all data from all user tables and views. To hide fields like card numbers or emails, you need a layer that rewrites matched fields in query results.

The query runs. The emails stay hidden.