I had a user unable to use a script which generated an email, I found the user didn’t have permission, Microsoft state:
To reduce the surface area of SQL Server, Database Mail stored procedures are disabled by default.
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.
You can add the user manually by:
Opening SQL Server Management Studio
Connecting to the server in question
Expand System Databases
Expand Users, right click the user or group and click properties
If the user is not in the list right click users and add the user or group
On the Membership tab ensure the user or group is a member of DatabaseMailUserRole
If you are lazy use a script like the one below (It will return a 1 if SQL can’t find the user or the user already has access, 0 means it was successful)
EXEC msdb.sys.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<AD_or_SQL_Username>'
I found using Windows Active Directory Groups required an extra step, by default all users have access to the Public Profile but not groups on SQL Mail.
Groups need to be added via “Manage Profile Security” –> “Private Profiles” tab.
As the group would be added select the AD group from the drop down box.
Ensure Access is ticked and click Next and OK.
As I have only done this the once I haven’t looked up if this can be done directly in T-SQL.