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]
-- 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

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.