add the send mail permission of non-sysadmin user

source text: https://www.c-sharpcorner.com/UploadFile/c1f7d5/sql-database-mail-profile-name-is-not-valid-in-sql-server-2/ 

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 = 'test@test.com'

, @subject = 'Automated Test Results (Successful)'

, @body = 'The stored procedure finished successfully.'

The error was:

Msg 229, Level 14, State 5, Procedure msdb..sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

You can see that the non-sysadmin user has been added to the DatabaseMailUserRole role in the database.

I try again of send mail.

The  new error was:

Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 141
Profile name is not valid

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.

 

Click the "Next" button.

 

Select "Manage profile security" then click the "Next" button.

 

Now go to Public Profiles, select your profile name and then set the profile to Public then click the "Next" button .

 

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.

 

Now you can send emails using the msdb.dbo.sp_send_dbmail Stored Procedure as a non-sysadmin user in SQL Server 2012.