This problem occured when I was trying to send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 2012.
For Example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile'
, @recipients = '[email protected]'
, @subject = 'Automated Test Results (Successful)'
, @body = 'The stored procedure finished successfully.'
The error was:
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 141
Profile name is not valid
You can see that the non-sysadmin user has been added to the DatabaseMailUserRole role in the database.
![Database Role Membership]()
Note: If the database mail profile is not set for public access then the user is not allowed to use the specified profile.
Now set the profile to Public.
Right-click on the Database Mail in the Management Studio and select the Configure Database Mail menu option.
![Configure Database Mail in SQL]()
Click the "Next" button.
![Database Mail Configure Wizard]()
Select "Manage profile security" then click the "Next" button.
![Configuration Task in Database Mail]()
Now go to Public Profiles, select your profile name and then set the profile to Public then click the "Next" button .
![Public Profiles in Database Mail]()
Note: The Manage Profile Security screen allows you to set this profile to either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (Microsoft database) to send e-mail using this profile.
Now click the "Finish" button.
![Manage Profile Security in Database Mail]()
Now you can send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 2012.