To send EMail from Other than SQL Agent Login in Database Mail:
User attempting to send Database Mail does not have permission to execute sp_send_dbmail.
The error text is:
EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.
So we can do it by following two methods.
(Or)
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
,@membername = '<user or role name>';
GO
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
,@membername = 'TestDBmailUser';
GO
When a user has sent mail u can see message in your Mail box also we can see Mail status
Unsent/Failed Messages:
select mailitem_id,sent_status,recipients,subject,body,body_format from sysmail_allitems
where sent_status not in ('Failed','unsent')
order by mailitem_id desc
Sent Messages:
select mailitem_id,sent_status,recipients,subject,body,body_format from sysmail_allitems
where sent_status in ('sent')
order by mailitem_id desc
You can also see When Mail Queue Mode before and After kindly see below
Before Mail Send it was Inactive mode and After you has sent Mail it was changed to
Receives_occuring mode.
Also u can see mail from your inbox
Scripts:
To Send Mail to Recipients:
DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
SET @p_profile_name = 'Test Notify'
SET @p_recipients = N'ashok.kumar@gmail.com;ashok.kumar@yahoo.com'
SET @p_subject = 'Test for email'
SET @p_subject = 'Test for email'
SET @p_body = 'test'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @p_profile_name,
@recipients = @p_recipients,
@body = @p_body,
@body_format = 'HTML',
@subject = @p_subject
To Check Status of Email Messages for delivered or not:
select mailitem_id,sent_status,recipients,subject,body,body_format from sysmail_allitems
where sent_status not in ('Failed','unsent')
order by mailitem_id desc
select mailitem_id,sent_status,recipients,subject,body,body_format from sysmail_allitems
where sent_status in ('sent')
order by mailitem_id desc
To Check Failditems:
SELECT sent_status,sent_date,items.last_mod_date,items.subject,items.last_mod_user,send_request_user,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
order by last_mod_date desc
To Check Mail Queue:
sysmail_help_queue_sp @queue_type = 'Mail' ;
