sql server - '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.

question from:https://stackoverflow.com/questions/3491053/profile-name-is-not-valid-error-when-executing-the-sp-send-dbmail-command

Answer 1

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 ;