I have a trigger set up on an insert in a table in my production database. When this fires, I want it to send an email if failure conditions are met. I was getting the standard permission errors with sp_send_dbmail but I finally got it to work by adding the user account to msdb db and placing it in the DatabaseMailUserRole group.
My issue is that I have upwards of a hundred users with new ones coming on all the time. In the production database, they all belong to their own database role and get assigned to this when new ones are created.
Is it possible to just add that database role in my production db to the msdb database and grant it execute permission so I will no longer have to worry about adding each user and new users as they come on board? If so, please include the syntax as everything I try does not seem to work.
P.S. This is on a SQL Server 2008 box.