Granting Sendmail to Login in SQL Server

Granting sendmail to a login/user is a common request that we DBAs get from developers.  They usually need to send reports via email on their stored procedures.

Here are the steps to how I grant them this privilege.

First, create a database role in the msdb database.

 

USE [msdb]
GO
CREATE ROLE [DatabaseMailUserRole]
GO

Then grant permissions to the new role.

USE [msdb]

GO

GRANT SELECT ON dbo.sysmail_allitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_sentitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_unsentitems TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_faileditems TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sysmail_delete_mailitems_sp TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_mailattachments TO [DatabaseMailUserRole];
GRANT SELECT ON dbo.sysmail_event_log TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sysmail_help_status_sp TO [DatabaseMailUserRole];
GRANT EXECUTE ON dbo.sp_send_dbmail TO [DatabaseMailUserRole];

Finally, grant this role to the login.

USE [msdb]
GO
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [LoginID]
GO