How to Identify Azure AD (Entra ID) Users and Groups with Database Access

How to Identify Azure AD (Entra ID) Users and Groups with Database Access

 In environments where databases use Microsoft Entra ID (formerly Azure Active Directory) for authentication, it is important to understand which users and groups have access to a database and what permissions they possess. SQL Server and Azure SQL Database store this information in system catalog views that can be queried directly.

This article explains two useful queries that help administrators identify:

  • Entra ID users and groups configured in the database
  • Database roles assigned to those users and groups

1. List All Entra ID Users and Groups

The following query displays all Microsoft Entra ID users and groups that exist as database principals:

SELECT name, type_desc
FROM sys.database_principals
WHERE type_desc IN ('EXTERNAL_USER', 'EXTERNAL_GROUP');

What the Query Does

The query retrieves records from the sys.database_principals system view and filters the results to show:

Type                             Description
EXTERNAL_USER Individual user account from Microsoft Entra ID
EXTERNAL_GROUP Security group from Microsoft Entra ID

Sample Output

name                                 type_desc
[email protected] EXTERNAL_USER
AirNavX-DB-Users         EXTERNAL_GROUP
SAS-Reporting-Team         EXTERNAL_GROUP

Use Cases

  • Validate whether a specific Entra ID user has been provisioned in the database.
  • Find all security groups granted database access.
  • Audit database authentication configuration.
  • Support access reviews and compliance activities.

2. Identify Database Roles Assigned to Users and Groups

After identifying database principals, the next step is understanding what permissions they have. The following query shows database role memberships:

SELECT
 dp.name,
 dp.type_desc,
 rp.name AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals rp
 ON drm.role_principal_id = rp.principal_id
JOIN sys.database_principals dp
 ON drm.member_principal_id = dp.principal_id
ORDER BY dp.name;

What the Query Does

This query joins:

  • sys.database_role_members
  • sys.database_principals (for members)
  • sys.database_principals (for roles)

to show which users or groups belong to which database roles.

Sample Output

name                                type_desc                     role_name
AirNavX-DB-Users EXTERNAL_GROUP     db_datareader
AirNavX-DB-Users EXTERNAL_GROUP     db_datawriter
[email protected] EXTERNAL_USER db_owner
SAS-Reporting-Team EXTERNAL_GROUP     db_datareader

Common Database Roles

Role                 Purpose
db_owner         Full control of the database
db_datareader         Read access to all tables and views
db_datawriter         Insert, update, and delete data
db_ddladmin         Create and modify database objects
db_securityadmin Manage permissions and role memberships

 By using both queries together, database administrators can answer the following questions:

  1. Which Microsoft Entra ID users and groups have access to the database?
  2. Which database roles have been assigned to them?
  3. Are permissions aligned with the principle of least privilege?
  4. Are there obsolete groups or users that should be removed?

For example:

  • If X_DB-Users appears as an EXTERNAL_GROUP, all members of that Entra ID group may inherit database access.
  • If the group is assigned to db_datareader, members can read all tables.
  • If the group is assigned to db_owner, members effectively have full administrative control over the database.

Recommended Administrative Checks

When investigating database access:

Check Entra ID Users and Groups:

SELECT name, type_desc
FROM sys.database_principals
WHERE type_desc IN ('EXTERNAL_USER', 'EXTERNAL_GROUP');

Check Role Assignments:

SELECT
 dp.name,
 dp.type_desc,
 rp.name AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals rp
 ON drm.role_principal_id = rp.principal_id
JOIN sys.database_principals dp
 ON drm.member_principal_id = dp.principal_id
ORDER BY dp.name;

Verify Group Membership in Azure Portal

After identifying an EXTERNAL_GROUP, navigate to:

Azure Portal → Microsoft Entra ID → Groups → [Group Name] → Members

This allows you to see the actual users who inherit database access through that group.

Conclusion

These two queries provide a quick and effective way to audit database access in environments that leverage Microsoft Entra ID authentication. Together, they help administrators identify both the users/groups present in the database and the permissions assigned to them through database roles, improving security governance and simplifying access reviews.

Post a Comment

0 Comments