'profile name is not valid' error when executing the sp_send_dbmail command

Question

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.

Sam · Answer 1 · 2010-08-18T23:18:55.947

51

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 ;

edited Aug 18 '10 at 23:18

answered Aug 17 '10 at 00:21

Sam

7,543
7
48
62

7

This error can also happen if you've specified the WRONG profile name via some types of code... i.e., sp_send_dbmail @profile_name = N'Some Profile That does Not Exist', etc... will throw this error as well. – Michael K. Campbell Dec 29 '14 at 21:36
2

As a heads-up, this same error can ALSO be caused if/when you've simply forgotten to let the SQL Server Agent have access to the SQL Server's Database Mail interfaces - as per this link: http://sqlmag.com/blog/avoiding-failed-notify-operator-email-errors – Michael K. Campbell Nov 11 '15 at 23:44
Shout out to @MichaelK.Campbell who's comment above helped me fix this! – sam.tldr Apr 06 '21 at 00:42
2

Looks like a dead link :( – Hecatonchires Jun 16 '22 at 04:08

score 27 · Answer 2 · edited Sep 16 '20 at 21:14

27

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.

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

edited Sep 16 '20 at 21:14

TylerH

20,799
66
75
101

answered Jan 27 '15 at 05:43

user3818587

303
4
8

2

What if you don't have "NT AUTHORITY\NETWORK SERVICE" as a user in your particular instance of SQL? i resolved it another way. See my answer above. – Fandango68 Nov 14 '17 at 01:42
2

Note: for me I just needed to check my profile name as a public profile. Worked for what I needed in my dev environment. Did not need NT AUTHORITY\NETWORK SERVICE step. – Taylor Brown Feb 28 '20 at 22:47

score 7 · Answer 3 · answered Nov 14 '17 at 01:40

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

score 3 · Answer 4 · answered Jan 03 '19 at 17:56

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.

score 1 · Answer 5 · answered Jul 03 '18 at 16:02

1

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.

answered Jul 03 '18 at 16:02

MTAdmin

1,023
3
17
36

score 0 · Answer 6 · answered May 12 '22 at 15:43

In case you are using SQL Managed Instance (SQL MI) as I was, the answer is that you need to create a Database Mail Profile called "AzureManagedInstance_dbmail_profile" which is automatically used by its SQL Server Agent. This works for sending job completion notifications.

'profile name is not valid' error when executing the sp_send_dbmail command

6 Answers6