SE の雑記

$TenantID="<Tenant ID>"
$objectId = "<Managed Identity object id>"


Connect-AzureAD -TenantId $TenantID

$PermissionName = "User.Read.All"
$GraphServicePrincipal = Get-AzureADServicePrincipal -SearchString "Microsoft Graph" | Select-Object -first 1

$AppRole = $GraphServicePrincipal.AppRoles | `
Where-Object {$_.Value -eq $PermissionName -and $_.AllowedMemberTypes -contains "Application"}

New-AzureAdServiceAppRoleAssignment -ObjectId $objectId -PrincipalId $objectId -ResourceId $GraphServicePrincipal.ObjectId -Id $AppRole.Id

# 削除する場合は以下を実行
$srAssignment = Get-AzureADServiceAppRoleAssignment -ObjectId $GraphServicePrincipal.ObjectId | ? PrincipalId -eq $objectId
Remove-AzureADServiceAppRoleAssignment -ObjectId $GraphServicePrincipal.ObjectId -AppRoleAssignmentId $srAssignment.ObjectId

IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 'https://graph.microsoft.com/v1.0/users')
BEGIN
    DROP DATABASE SCOPED CREDENTIAL [https://graph.microsoft.com/v1.0/users];
END

CREATE DATABASE SCOPED CREDENTIAL [https://graph.microsoft.com/v1.0/users] 
WITH 
    IDENTITY = 'Managed Identity'
    , SECRET = '{"resourceid":"https://graph.microsoft.com/"}';
GO


DECLARE @response nvarchar(max)
EXEC sp_invoke_external_rest_endpoint 
    @url = 'https://graph.microsoft.com/v1.0/users'
    , @method='GET'
    , @response = @response OUTPUT
    , @credential = [https://graph.microsoft.com/v1.0/users]

IF (SELECT JSON_VALUE(@response, '$.response.status.http.code')) <> 200
BEGIN
    SELECT [key],[value] FROM OPENJSON(@response, '$.response.headers')
    UNION ALL
    SELECT [key],[value] FROM OPENJSON(@response, '$.result.error')
END
ELSE
BEGIN
    SELECT 
        JSON_VALUE(value, '$.displayName') AS displayName ,
        JSON_VALUE(value, '$.userPrincipalName') AS userPrincipalName 
    
    FROM OPENJSON(@response, '$.result.value')
END