How to Configure Azure SQL Service Account

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up an azure sql service account, create a login or contained user. Then map that identity to a database user with specific role permissions. Engineering teams often build one shared account for apps and human fixing. They assign broad roles so jobs do not fail during deploy steps. Over time, that shared account holds full write power across every user table. How an azure sql service account works In SQL Server and Azure SQL, a database user represents an identity inside one database. T

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.

To set up an azure sql service account, create a login or contained user. Then map that identity to a database user with specific role permissions. Engineering teams often build one shared account for apps and human fixing. They assign broad roles so jobs do not fail during deploy steps. Over time, that shared account holds full write power across every user table.

How an azure sql service account works

In SQL Server and Azure SQL, a database user represents an identity inside one database. Teams choose between two main patterns when they set up a new account. If an account must reach many databases on one instance, admins create a login first. That login lives in the master database and maps to users in other databases. If an account only needs one or a few databases, a contained user works well. A contained database user holds its own password inside the target database itself. It does not need a login in the master database at all. Before using contained users, an admin must turn on contained databases on the server. The admin must also turn on the setting on the target database. SQL Server supports thirteen total types of database users for different identity needs.

After creating the user, admins assign permissions through fixed database roles. Every database user starts in the public role, which includes basic connect rights. You cannot change the built-in permissions assigned to any fixed database role. For read tasks, admins add the user to the standard reader role. Members of that reader role can read all data from all user tables and views. For apps that change data, admins add the user to the standard writer role. Members of that writer role can add, delete, or change data in all user tables. Some teams also use deny roles to block read or write actions on tables. A deny reader role stops the user from reading any user tables, views, or metadata. A deny writer role stops the user from adding, changing, or deleting any table data.

Why fixed roles leave runtime gaps

Avoid putting an app or support account into the database owner role. Members of the owner role can run all setup and maintenance tasks. They can even drop the entire database. Even when you avoid the owner role, fixed roles remain very broad. The standard reader role lets an account read every single user table and view. It cannot hide specific columns that hold emails, phone numbers, or card numbers. The standard writer role lets an account delete or change rows in any user table.

Native roles only check who logs in at the start of a session. They do not inspect what individual queries do while the session runs. If an engineer uses a service account for support, every action looks the same. Audit logs show the shared service account name instead of the actual person. Also, a single bad query under a writer role can wipe out a table. Database roles alone cannot require peer approval before a risky delete statement runs. They also cannot rewrite sensitive fields like national IDs in query results on the fly.

Service-to-Service Authentication + Service Account Governance: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Scoping permissions for apps and engineers

Start by splitting human access from automated app traffic. Give each background worker its own database user with only the roles it needs. If a reporting job only reads three tables, do not grant the full reader role. Grant select rights on those specific tables or views instead. Use deny roles when you must block a user from reading or changing data. Keep human engineers out of shared service accounts during daily support work. When people share one password, you lose visibility into who ran which query. You also have to rotate keys across every app when one person leaves the team.

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

When should I use a contained user for an azure sql service account?

Use a contained database user when the account only needs one or a few databases. It keeps the user and password inside the database without a master login.

What is the risk of the standard reader role for service accounts?

The standard reader role grants read access to all user tables and views. The account can read sensitive columns like emails, phone numbers, and card numbers everywhere.

Can I edit the permissions inside a fixed database role?

No, SQL Server does not let you change the permissions of fixed database roles. To use custom permissions, grant table rights directly or create a custom role.

The query runs. The emails stay hidden.