LLI Wiki — My Digital Knowledge Base

Entra ID group membership and SQL permissions audit queries

List a user's groups and group members with PowerShell, then audit database users, roles and permissions in SQL.

When SQL access is granted through Microsoft Entra ID (Azure AD) groups, you often need to answer two questions: which groups a user belongs to, and what those groups can do in a database. This article lists the PowerShell and T-SQL snippets for both.

List the groups of a user

With the AzureAD module:

Connect-AzureAD
Get-AzureADUser -ObjectId "user@example.com" | Get-AzureADUserMembership | Select DisplayName

Example output (group names are generic examples):

DisplayName
azgr-prd-app1-sql-user
azgr-prd-app2-sql-user
azgr-prd-TechnicalAccounts

The same with Microsoft Graph:

# Install Microsoft Graph module (if not already)
Install-Module Microsoft.Graph -Scope CurrentUser

# Connect to Microsoft Graph
Connect-MgGraph -Scopes "User.Read.All", "GroupMember.Read.All"

# Replace with the user's UPN
Get-MgUser -UserId "user@example.com" | Get-MgUserMemberOf | Select DisplayName

List the members of groups

<#
1. Install-Module AzureAD
   Import-Module AzureAD
   Get-Module AzureAD
#>

Connect-AzureAD

$groups = @(
    'azgr-dev-app-sql-admin'
)

foreach ($group in $groups)
{
    $a = Get-AzureADGroup -SearchString $group
    $a.DisplayName

    Get-AzureADGroupMember -ObjectId $a.ObjectId | Sort-Object DisplayName | Format-Table DisplayName, ObjectId
}

List the users of the current database

SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type IN ('S', 'E', 'X', 'G', 'U') -- SQL, External, Windows, Groups, Users
  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys');

List roles and their members

SELECT
    dp1.name AS RoleName,
    dp2.name AS MemberName
FROM
    sys.database_role_members drm
JOIN
    sys.database_principals dp1 ON drm.role_principal_id = dp1.principal_id
JOIN
    sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
ORDER BY
    dp1.name, dp2.name;

Permissions granted to roles or users

SELECT
    dp.name AS PrincipalName,
    dp.type_desc AS PrincipalType,
    p.class_desc,
    p.permission_name,
    p.state_desc,
    OBJECT_NAME(p.major_id) AS ObjectName
FROM
    sys.database_permissions p
JOIN
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
ORDER BY
    PrincipalName, ObjectName, permission_name;

Permissions of a specific role or user

SELECT
    dp.name AS PrincipalName,
    p.permission_name,
    p.state_desc,
    OBJECT_NAME(p.major_id) AS ObjectName
FROM
    sys.database_permissions p
JOIN
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE
    dp.name = 'role-app-sql-user';
Mastodon LLIMastodon SocialFosstodon