Hi,
I have a windows account with users group and trying to exec the 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.. can anyone pleas help?
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 ;