Msg 14607, Level 16, State 1 – profile name is not valid

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 = 'cms4j@wordpress.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:
http://www.sqlservercentral.com/Forums/Topic698578-146-1.aspx

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.

So i opened Database Mail Configuration Wizard from within the SSMS:
Image

chose “Manage Profile Security”
Image

and found that all my mail profiles were public:
Image

But wait, there is another tab “Private Profiles” that shows more options:
Image

So i selected our SQL Server Login from the drop down list and granted access to all existing mail profiles.
Image
Clicking next and finishing i tried to send an email once again, and it

WORKED 🙂

Thank you for reading, i hope it solved your problem too.

Advertisements
This entry was posted in SQL Server and tagged , . Bookmark the permalink.

One Response to Msg 14607, Level 16, State 1 – profile name is not valid

  1. jeff says:

    Worked like a champ. Thanks for making this available.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s