This problem occurred 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.
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.