To set up SQL Server Agent Mail to use Database Mail
· In Object Explorer, expand a server.
· Right-click SQL Server Agent, and then click Properties.
· Click Alert System.
· Select Enable Mail Profile.
· In the Mail system list, choose Database Mail.
· In the Mail profile list, select a mail profile for Database Mail.
--Not entirely sure if this works
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
--Shows principle id, name and profile (not sure if it’s helpful, but included anyways)
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
--To start Database Mail; although it does start itself when needed
EXEC msdb.dbo.sysmail_start_sp;
--See Sent email
SELECT * FROM msdb.dbo.sysmail_sentitems;
--Email event log to see if info, warnings, and errors
SELECT * FROM msdb.dbo.sysmail_event_log;
--To get number of unsent emails
select count(*) from msdb.dbo.sysmail_unsentitems;
-- remove all the unsent emails
--delete from msdb.dbo.sysmail_unsentitems;
http://msdn2.microsoft.com/en-us/library/ms186358(d=ide).aspx.
https://www.sqlservercentral.com/Forums/Topic263951-92-1.aspx