the solutions is fairly simple, but it varies a bit based on your specific setup. it all boils down to the group or login needs to be part of the built inDatabaseMailUserRole in msdb

lets assume this scenario;

a Windows Login [mydomain\Clarkkent or SQL Login [ClarkKent] is being used to get to the PRODUCTION database, and you want to let that user use the procedure that uses dbmail. that login has a matching USER in the PRODUCTION database we talked about.

you would go to the msdb database, and add that user to the msdb database also.

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'