Database mail does not work? Profile not valid? Check this out.
We use Database Mail very intensive, though faced a problem today with a new server login that we added to our instance. Every time we tried to send an email like this:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'My profile name', @recipients = 'email@example.com', @subject = 'Test', @body = 'Message', @body_format = 'HTML';
We got the error message:
Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119 profile name is not valid
When i added our user with whom we connected to the SQL Server Instance to the Server Role “sysadmin” then the email was sent right away. But that had nothing do do with security if we added our dedicated user to the sysadmin-group.
So there had to be another solution.
I googled a bit and found the following post on sqlservercentral:
There was mentioned a solution like this:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @principal_name = 'public', @profile_name = 'My profile name', @is_default = 1 ;
I executed it, but it did not work.
Might be the different SQL Server version, in the post it was mentioned sql2k5, but we run sql2k8 on our machine.
Thank you for reading, i hope it solved your problem too.