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 = ''; 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_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' ;

Published by

S.G.Godwin Dinesh.MCA

Sr.System Administrator