dbatools
dbatools copied to clipboard
Get-DbaDbMailAccount & Get-DbaDbMailProfile to return Account-Profile link details
Summarize Functionality
In SSMS it's possible to see database mail accounts configured for the database mail profile
Similar to details in
select * from msdb.dbo.sysmail_profileaccount
I would propose adding this functionality to either Get-DbaDbMailAccount or Get-DbaDbMailProfile or even both. We could also create a separate cmdlet Get-DbaDbMailProfileAccount but it might be overkill.
Accounts have the GetAccountProfileNames method and Profiles the EnumAccounts method.
This way both commands would have the following output (new property added to the right):
Get-DbaDbMailAccount -SqlInstance $s1 -SqlCredential $credential | ft
ComputerName InstanceName SqlInstance ID Name DisplayName Description EmailAddress ReplyToAddress IsBusyAccount MailServers MailProfile
------------ ------------ ----------- -- ---- ----------- ----------- ------------ -------------- ------------- ----------- -----------
localhost MSSQLSERVER 6f34ddb8eb52 12 MailAccount MailAccountDisplay [email protected] False {} {MailProfile}
localhost MSSQLSERVER 6f34ddb8eb52 13 MailAccount2 MailAccountDisplay [email protected] False {} {MailProfile, MailProfile2}
Get-DbaDbMailProfile -SqlInstance $s1 -SqlCredential $credential | ft
ComputerName InstanceName SqlInstance ID Name Description ForceDeleteForActiveProfiles IsBusyProfile MailAccount
------------ ------------ ----------- -- ---- ----------- ---------------------------- ------------- -----------
localhost MSSQLSERVER 6f34ddb8eb52 164 MailProfile True False {MailAccount, MailAccount2}
localhost MSSQLSERVER 6f34ddb8eb52 165 MailProfile2 True False MailAccount2
I also have a draft for Set-* function, so will open the FR later.
Is there a command that is similiar or close to what you are looking for?
No
Technical Details
I tried this way - wrap both lists in a loop to add the Profiles or Accounts.
Get-DbaDbMailAccount https://github.com/sqlcollaborative/dbatools/blob/2d4ac0da63f1845caa76aaf65a535e93d8c197b7/functions/Get-DbaDbMailAccount.ps1#L98-L101
foreach ($acct in $accounts) {
$acct | Add-Member -Force -MemberType NoteProperty -Name ComputerName -value $mailserver.ComputerName
$acct | Add-Member -Force -MemberType NoteProperty -Name InstanceName -value $mailserver.InstanceName
$acct | Add-Member -Force -MemberType NoteProperty -Name SqlInstance -value $mailserver.SqlInstance
$acct | Add-Member -Force -MemberType NoteProperty -Name MailProfile -value $acct.GetAccountProfileNames()
}
$accounts | Select-DefaultView -Property ComputerName, InstanceName, SqlInstance, ID, Name, DisplayName, Description, EmailAddress, ReplyToAddress, IsBusyAccount, MailServers, MailProfile
Get-DbaDbMailProfile https://github.com/sqlcollaborative/dbatools/blob/2d4ac0da63f1845caa76aaf65a535e93d8c197b7/functions/Get-DbaDbMailProfile.ps1#L106-L110
foreach ($prof in $profiles) {
$prof | Add-Member -Force -MemberType NoteProperty -Name ComputerName -Value $mailserver.ComputerName
$prof | Add-Member -Force -MemberType NoteProperty -Name InstanceName -Value $mailserver.InstanceName
$prof | Add-Member -Force -MemberType NoteProperty -Name SqlInstance -Value $mailserver.SqlInstance
$prof | Add-Member -Force -MemberType NoteProperty -Name MailAccount -Value $prof.EnumAccounts().AccountName
}
$profiles | Select-DefaultView -Property ComputerName, InstanceName, SqlInstance, ID, Name, Description, ForceDeleteForActiveProfiles, IsBusyProfile, MailAccount
Hi Mikey
thanks for this: the profile-account association is indeed missing and interesting. Since in both cases the associations are fetched via a method, I assume it adds some searching. If this is minimal, I'm in favour of the way you demo. If the delay is noticeable, it's better to only provide it with a switch param, or indeed put it in a separate function. Do you have some metrics about the added functionality?
Hey, @PowerDBAKlaas sorry no metrics for that.