[sql-server] 'profile name is not valid' error when executing the sp_send_dbmail command

I have a windows account with users group and trying to exec sp_send_dbmail but getting an error:

profile name is not valid.

However, when I logged in as administrator and execute the sp_send_dbmail, it managed to send the email so obviously the profile name does exist on the server.

You need to grant the user or group rights to use the profile. They need to be added to the msdb database and then you will see them available in the mail wizard when you are maintaining security for mail.

Read up the security here: http://msdn.microsoft.com/en-us/library/ms175887.aspx

See a listing of mail procedures here: http://msdn.microsoft.com/en-us/library/ms177580.aspx

Example script for 'TestUser' to use the profile named 'General Admin Mail'.

USE [msdb]
GO
CREATE USER [TestUser] FOR LOGIN [testuser]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'TestUser'
GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'General Admin Mail',
    @principal_name = 'TestUser',
    @is_default = 1 ;

I got the same problem also. Here's what I did:

If you're already done granting the user/group the rights to use the profile name.

1. Go to the configuration Wizard of Database Mail
2. Tick Manage profile security
3. On public profiles tab, check your profile name
4. On private profiles tab, select NT AUTHORITY\NETWORK SERVICE for user name and check your profile name
5. Do #4 this time for NT AUTHORITY\SYSTEM user name
6. Click Next until Finish.

profile name is not valid [SQLSTATE 42000] (Error 14607)

This happened to me after I copied job script from old SQL server to new SQL server. In SSMS, under Management, the Database Mail profile name was different in the new SQL Server. All I had to do was update the name in job script.

In my case, I was moving a SProc between servers and the profile name in my TSQL code did not match the profile name on the new server.

Updating TSQL profile name == New server profile name fixed the error for me.

Did you enable the profile for SQL Server Agent? This a common step that is missed when creating Email profiles in DatabaseMail.

Steps:

• Right-click on SQL Server Agent in Object Explorer (SSMS)
• Click on Properties
• Click on the Alert System tab in the left-hand navigation
• Enable the mail profile
• Set Mail System and Mail Profile
• Click OK
• Restart SQL Server Agent