LLI Wiki — My Digital Knowledge Base

Groupes Entra ID et permissions SQL : requêtes d'audit

Lister les groupes d'un utilisateur et leurs membres avec PowerShell, puis auditer utilisateurs, rôles et permissions d'une base SQL.

Lorsque l'accès SQL est accordé via des groupes Microsoft Entra ID (Azure AD), deux questions reviennent : à quels groupes appartient un utilisateur, et que peuvent faire ces groupes dans une base ? Cet article regroupe les extraits PowerShell et T-SQL correspondants.

Lister les groupes d'un utilisateur

Avec le module AzureAD :

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

Exemple de résultat (noms de groupes génériques) :

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

Le même résultat avec Microsoft Graph :

# Installer le module Microsoft Graph (si nécessaire)
Install-Module Microsoft.Graph -Scope CurrentUser

# Se connecter à Microsoft Graph
Connect-MgGraph -Scopes "User.Read.All", "GroupMember.Read.All"

# Remplacer par l'UPN de l'utilisateur
Get-MgUser -UserId "user@example.com" | Get-MgUserMemberOf | Select DisplayName

Lister les membres de groupes

<#
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
}

Lister les utilisateurs de la base courante

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');

Lister les rôles et leurs membres

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 accordées aux rôles ou utilisateurs

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 d'un rôle ou utilisateur précis

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