How to grant sendmail permission to sql server user?
Please try with the below 2 options.
USE msdb;--add our userCREATE USER ClarkKent FOR LOGIN ClarkKent; --give this user rights to use dbmailexec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'
now if we know ClarkKent is getting his authorization from a windows group, then you add that windows group as a user, and add that group to the same role;
USE msdb;--add our user via a group we know he is in CREATE USER 'mydomain\BusinessGroup' FOR LOGIN 'mydomain\BusinessGroup'; --give this GROUP rights to use dbmailexec sp_addrolemember 'DatabaseMailUserRole', 'mydomain\BusinessGroup'
You need to assign the login (or a role) to the mail profile after adding it to the database mail role. For the script below, a default of one may need to be set, instead of zero.
use msdbexec sp_addrolemember 'DatabaseMailUserRole', 'sqlUser'EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @principal_name = 'sqlUser', @profile_name = 'sqlMailProfileName',@is_default = 0