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