Skip to main content

Profile name is not valid - Error when sending an email using sp_send_dbmail in SQL Server

A week ago I shared some posts related to replication and scheduled jobs information and you may enjoy it. Hope you liked it too. While working with security, suddenly I started to receive an error when sending an email though script using sp_send_dbmail from msdb database specifically for one user and I clicked it was due to changes in access of that user. The analysis was going long and checked user access to msdb databases and it has db_datareader, and DatabaseMailUserRole and failed to send an email. Even it was not working, assigned db_owner to that user in msdb database.
Finally came to solution using sysmail_add_principalprofile_sp system object which grants permission for a database user or role to use a specified Database Mail profile,
USE [msdb]
GO
-- DatabaseMailUserRole database role should be assigned to user if user is not db_owner database role and sysadmin server role
EXEC sp_addrolemember N'DatabaseMailUserRole', N'UserName' -- Put user name here
GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'ProfileName', -- Put pfofile name here
    @principal_name = 'UserName', -- Put user name here
    @is_default = 1 ; 
YYou can also make the same changes from the user interface. Goto Database Mail, right click and goto Configure Database Mail , select an option Manage Profile Security, Go to Private Profiles Tab, Select User name and check the box of Access and make a default profile to Yes for the profile name using which we want to send an email.


After above change it was succeeded to send an email. Stay tuned for more.

Comments