How to grant sendmail permission to sql server user?

Question

I have a database user which is the owner of the database. The application requirement is to send mail using database mail of sql server .

Is there any way that I can add grant only send mail permission to that user?

I have a user named testuser having server roles public and is db_owner for 1 database. Please tell me the way that I don`t need to give sysadmin serverroles to that user.

Answer 1

Please try with the below 2 options.

USE msdb;
--add our user
CREATE USER  ClarkKent FOR LOGIN  ClarkKent; 
--give this user rights to use dbmail
exec 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 dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'mydomain\BusinessGroup'