Database Mail profiles do not allow adding more than one account

[danielleshoemake]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [22:44:11][New-DbaDbMailProfile] Failure | Violation of UNIQUE KEY constraint 'SYSMAIL_PROFILE_NameMustBeUnique'. Cannot insert duplicate key in object 'dbo.sysmail_profile'. The duplicate key value is (INGAA LLR Website Email Sender Profile). The statement has been terminated.

Steps to Reproduce

New-DbaDbMailProfile -SqlInstance localhost -Profile "Test profile" -MailAccountName "Account 1" -MailAccountPriority 1
New-DbaDbMailProfile -SqlInstance localhost -Profile "Test profile" -MailAccountName "Account 2" -MailAccountPriority 2

Upon executing the second command, where the account name and priority has changed, a unique key constraint violation is issued. It seems that there is an attempt to recreate the entire profile versus checking if the profile already exists and then appending an account to the existing profile. It appears that creating the profile and adding an account to the profile are already two separate steps, but the fact that this is a one to many relationship is not accounted for.

Please confirm that you are running the most recent version of dbatools

2.1.3

Other details or mentions

Profiles can have multiple accounts, each is given a priority. The New-DbaDbMailProfile module configures priority, but it seems it only allows adding one account.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.3770
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.3770
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version

---

Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0

[potatoqualitee]

oof, rough bug. @claude pls review this issue and provide a solution

[claude[bot]]

Claude finished @potatoqualitee's task —— View job • claude/issue-9336-20251128-1641 • Create PR ➔

---

Database Mail Profile Fix Complete

• [x] Read and understand the issue
• [x] Locate and analyze New-DbaDbMailProfile source code
• [x] Identify the bug (profile recreation instead of account addition)
• [x] Implement fix to check for existing profile
• [x] Review and update tests
• [x] Commit and push changes
• [x] Provide PR link

Analysis

Found the bug in /home/runner/work/dbatools/dbatools/public/New-DbaDbMailProfile.ps1:

Issue: The command always attempted to create a new profile object, even if the profile already existed. When called a second time with the same profile name, it would try to create a duplicate profile, causing the UNIQUE KEY constraint violation reported in the issue.

Root Cause: The command didn't check if a profile already exists before attempting to create it, ignoring the one-to-many relationship between profiles and accounts.

Solution Implemented

Modified the command to properly handle the one-to-many relationship:

1. Check for existing profile - Query $server.Mail.Profiles to see if the profile already exists
2. If profile exists with MailAccountName - Add the account to the existing profile using AddAccount()
3. If profile exists without MailAccountName - Display a helpful error message explaining that the profile already exists
4. If profile doesn't exist - Create the profile as before (original behavior)

Changes Made

File: /home/runner/work/dbatools/dbatools/public/New-DbaDbMailProfile.ps1

• Added existence check for profiles before creation
• Added conditional logic to handle existing vs new profiles
• Maintained backward compatibility for new profile creation
• Improved error messaging for duplicate profile scenarios

File: /home/runner/work/dbatools/dbatools/tests/New-DbaDbMailProfile.Tests.ps1

• Created second test mail account for multi-account testing
• Added new test context "Adds multiple accounts to the same profile"
• Added test to verify multiple accounts can be added without errors
• Added test to verify both accounts are properly associated
• Added test to verify appropriate error message for duplicate profile attempts

Usage Example

The fix now allows the exact scenario from the issue report:

# First call - creates profile with first account
New-DbaDbMailProfile -SqlInstance localhost -Profile "Test profile" -MailAccountName "Account 1" -MailAccountPriority 1

# Second call - adds second account to existing profile (no longer throws error!)
New-DbaDbMailProfile -SqlInstance localhost -Profile "Test profile" -MailAccountName "Account 2" -MailAccountPriority 2

---

Branch: claude/issue-9336-20251128-1641 |