Doing the following:
- created a wrapper procedure in msdb that calls sp_send_dbmail
- granted sql user exec permissions to my wrapper procedure
- added the user to the DatabaseMailUserRole in msdb
- changed the mail profile being used, from private to public in database mail config area
- user will initiate the wrapper procedure via an insert trigger on a table in a different database
I changed the mail profile from private to public to allow the sql user in this context to call sp_send_dbmail, by way of the wrapper procedure, but not call sp_send_dbmail directly. With the mail profile as private, I get errors such as
Msg 15404, Level 16, State 10, Procedure xp_logininfo, Line 62 Could not obtain information about Windows NT group/user '... user name here', error code 0xffff0002.
It works on my test system, and now I want to do this on my production system.
What are the security implications? Is changing the db mail profile from private to public a bad idea?
I have a feeling that you run into a cross-database issue here, but I can't say for sure since Db Mail is involved. However, I suggest you check out
http://www.sommarskog.se/grantperm.html the "Cross-Database Access" sections.