eSHARS Database Access Strategy

Overview

eSHARS developer access to the Azure SQL databases (data, log, audit) is managed via seven Entra ID security groups in the HISD tenant. Each group maps to a specific (environment tier, access profile) — the SQL-side role grants encode the per-database permissions. A developer’s effective access is the union of all groups they belong to.

This doc is for engineers granting or revoking database access, and for onboarding new developers. See INFRA-0006 for the rationale behind this model.

---

Access Model

Environments and databases

Tier	Environments	Databases
nonprod	qa, uat, training, regression	data, log, audit
prod	prod	data, log, audit

Dev environment

The dev environment uses local developer databases — no shared cloud DB access is needed. No groups or SQL grants exist for the dev environment.

UAT placement deviation (temporary)

As of 2026-04-26, the UAT databases (db-eshars-uat-data, db-eshars-uat-log, db-eshars-uat-audit) reside on the prod SQL server, not the nonprod server. As a result, the three nonprod-* groups were provisioned directly on those UAT databases on the prod server. The grants are database-scoped and do not affect prod databases. This deviation will be resolved when UAT is migrated to the nonprod SQL server — at that point, remove the nonprod group users from the UAT databases on the prod server (DROP USER [sg-eshars-db-nonprod-*]) and verify the existing nonprod server grants are in place.

Groups

Group	data	log	audit	Typical user
sg-eshars-db-nonprod-reader	Read	Read	Read	QA engineer, analyst, junior dev
sg-eshars-db-nonprod-writer	Read-Write	Read	Read	Developer (baseline)
sg-eshars-db-nonprod-dba	DBA	DBA	DBA	Lead developer, DBA
sg-eshars-db-prod-reader	Read	—	—	Troubleshooting prod data
sg-eshars-db-prod-writer	Read-Write	Read	Read	Authorized hotfix / data-fix
sg-eshars-db-prod-auditor	—	—	Read	Compliance reviewer, auditor
sg-eshars-db-prod-dba	DBA	DBA	DBA	Formal DBA only

Mix-and-match rule

A developer gets 1–2 groups covering their access needs. The effective permission on any database is the union of all granted group permissions.

Example — standard developer (Markel Fennell):

• sg-eshars-db-nonprod-writer → RW on data, R on log/audit across qa/uat/training/regression
• sg-eshars-db-prod-reader → R on prod data only

---

Assignment Flow

Standard request (new dev / permission change)

1. Developer fills in the eSHARS Database Permissions SharePoint list.

2. Leon maps the response to group(s) using the Decision Table below.

3. Add the developer to the group(s):

   az login --tenant b07ee812-5705-40b5-b227-3df824080db8
   az ad group member add \
     --group sg-eshars-db-nonprod-writer \
     --member-id <user-object-id>

4. Access is live within ~15 minutes (Entra token refresh).

5. If the response doesn’t fit any standard pattern, add a direct SQL user and register it in the Exceptions Registry.

Decision Table

Access pattern requested	Groups to assign
Standard developer	nonprod-writer + prod-reader
QA / analyst / read-only nonprod	nonprod-reader
Lead dev / needs all nonprod	nonprod-dba + prod-reader
Formal DBA	nonprod-dba + prod-dba
Compliance / audit trail reviewer	prod-auditor
Authorized hotfix (time-boxed)	prod-writer — remove after incident closes
No prod access	nonprod group only, no prod group

---

Granting Permissions in SQL

These grants are one-time per group per database server. Run inside the target database (not master). They are idempotent — safe to re-run.

nonprod-reader (run in each nonprod data, log, and audit DB)

CREATE USER [sg-eshars-db-nonprod-reader] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-nonprod-reader];

nonprod-writer

-- Run in each nonprod DATA DB (qa, uat, training, regression)
CREATE USER [sg-eshars-db-nonprod-writer] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-nonprod-writer];
ALTER ROLE db_datawriter ADD MEMBER [sg-eshars-db-nonprod-writer];
GRANT EXECUTE ON SCHEMA::dbo TO [sg-eshars-db-nonprod-writer];

-- Run in each nonprod LOG and AUDIT DB (read-only + execute)
CREATE USER [sg-eshars-db-nonprod-writer] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-nonprod-writer];
GRANT EXECUTE ON SCHEMA::dbo TO [sg-eshars-db-nonprod-writer];

EXECUTE and ownership chaining

Do not grant EXECUTE ON SCHEMA::dbo to nonprod-reader. SQL Server’s ownership chaining means a stored proc can INSERT/UPDATE/DELETE on behalf of the caller without checking the caller’s table permissions — granting EXECUTE to a reader effectively makes them a writer.

nonprod-dba

-- Run in all nonprod DBs (data, log, audit × qa/uat/training/regression)
CREATE USER [sg-eshars-db-nonprod-dba] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [sg-eshars-db-nonprod-dba];

prod-reader

-- Run in prod DATA DB only
CREATE USER [sg-eshars-db-prod-reader] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-prod-reader];

prod-writer

-- Run in prod DATA DB
CREATE USER [sg-eshars-db-prod-writer] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-prod-writer];
ALTER ROLE db_datawriter ADD MEMBER [sg-eshars-db-prod-writer];
GRANT EXECUTE ON SCHEMA::dbo TO [sg-eshars-db-prod-writer];

-- Run in prod LOG and AUDIT DB (read-only + execute)
CREATE USER [sg-eshars-db-prod-writer] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-prod-writer];
GRANT EXECUTE ON SCHEMA::dbo TO [sg-eshars-db-prod-writer];

prod-auditor

-- Run in prod AUDIT DB only
CREATE USER [sg-eshars-db-prod-auditor] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sg-eshars-db-prod-auditor];

prod-dba

-- Run in all prod DBs (data, log, audit)
CREATE USER [sg-eshars-db-prod-dba] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [sg-eshars-db-prod-dba];

Flat membership only

Azure SQL resolves Entra group membership at login time but does not transitively resolve nested groups in all SKUs. Keep memberships flat: user → group directly. Do not nest groups inside groups.

---

Exceptions Registry

Individual SQL users for access patterns that don’t fit any standard group. Review quarterly — remove stale entries.

User	Database	Grant	Reason	Added	Review
(none yet)

Adding an exception

Create the SQL user directly in the target database with the minimum required role. Add a row to this table with the reason and a review date. Do not create a new Entra group for one-off patterns.

---

Revocation

Group removal — remove from the Entra group; access revokes within ~15 minutes:

az ad group member remove \
  --group sg-eshars-db-nonprod-writer \
  --member-id <user-object-id>

Offboarding checklist:

• Remove from all sg-eshars-db-* groups the user belongs to
• Check the Exceptions Registry above — drop any direct SQL users for this person
• Verify with entra-access-audit.sql that no residual access remains

---

Verification

Entra side — list members of a group:

az ad group member list --group sg-eshars-db-nonprod-writer --output table

SQL side — run entra-access-audit.sql (see Database scripts) against each database. It lists all Entra users/groups and their assigned roles. After the initial setup you should see exactly the groups relevant to that DB tier and no legacy individual user entries.

Smoke test after adding a user:

1. Add user to sg-eshars-db-nonprod-writer.
2. Wait 15 minutes.
3. Connect to a nonprod data DB as the user — SELECT TOP 1 * FROM <any table> and INSERT/UPDATE should both succeed.
4. Connect to a nonprod log or audit DB — SELECT should succeed, INSERT should fail with permission denied.

---

Related

• INFRA-0006: eSHARS Database Access Model — why this model was chosen
• Developer intake: eSHARS Database Permissions (SharePoint)
• Azure SQL AD Auth Migration — background on Entra-based SQL authentication
• entra-access-audit.sql — existing audit script to verify current DB role memberships