Hello,
Thanks for your question.
To do this I will recommend using a managed identity with the appropriate resource contributor permissions on least privilege scope
You can get the access token using:
$Token = (Get-AZAccessToken -ResourceUrl https://database.windows.net).Token
you can then use Invoke-Sqlcmd to execute the stored procedure requirement
https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
See an example here to execute stored procedure.
Invoke-Sqlcmd -ServerInstance $Server -Database $db -AccessToken $token `
-Query "exec [dbo].[procedure] @Operation='all', @LogToTable=1" `
-QueryTimeout 65535 -ConnectionTimeout 60 -Verbose
Also consider setting appropriate schedules and variables for your automation account depending on your use case.
https://learn.microsoft.com/en-us/azure/automation/shared-resources/schedules
https://learn.microsoft.com/en-us/azure/automation/shared-resources/variables?tabs=azure-powershell
Please let me know if you have further questions
You can mark it 'Accept Answer' if this helped.