Edit

Common errors with database mail

  • Article

Applies to: SQL Server Azure SQL Managed Instance

This article describes some common errors encountered with database mail and their solutions.

Could not find stored procedure 'sp_send_dbmail'

The sp_send_dbmail stored procedure is installed in the msdb database. You must either run sp_send_dbmail from the msdb database, or specify a three-part name for the stored procedure.

Example:

EXEC msdb.dbo.sp_send_dbmail ...

Or:

USE msdb;
GO
EXEC dbo.sp_send_dbmail ...

Use Database Mail Configuration Wizard to enable and configure database mail.

Profile not valid

There are two possible causes for this message. Either the profile specified does not exist, or the user running sp_send_dbmail (Transact-SQL) does not have permission to access the profile.

To check permissions for a profile, run the stored procedure sysmail_help_principalprofile_sp (Transact-SQL) with name of the profile. Use the stored procedure sysmail_add_principalprofile_sp (Transact-SQL) or the Database Mail Configuration Wizard to grant permission for a msdb user or group to access a profile.

Permission denied on sp_send_dbmail

This topic describes how to troubleshoot an error message stating that the 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.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>';
GO

For more information, see sp_addrolemember and sp_droprolemember.

Database mail queued, no entries in sysmail_event_log or Windows Application Event Log

Database Mail relies on Service Broker for queuing e-mail messages. If Database Mail is stopped or if Service Broker message delivery is not activated in the msdb database, Database Mail queues messages in the database but cannot deliver the messages. In this case, the Service Broker messages remain in the Service Broker Mail queue. Service Broker does not activate the external program, so there are no log entries in sysmail_event_log and no updates to the item status in sysmail_allitems and the related views.

Execute the following statement to check whether Service Broker is enabled in the msdb database:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

A value of 0 indicates that Service Broker message delivery is not activated in the msdb database. To correct the problem, activate Service Broker in the database with the following Transact-SQL command:

USE master ;
GO

ALTER DATABASE msdb SET ENABLE_BROKER ;
GO

Database Mail relies on a number of internal stored procedures. To reduce the surface area, these stored procedures are disabled on new installation of SQL Server. To enable these stored procedures, use the Database Mail XPs option of the sp_configure system stored procedure, as in the following example:

EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;  
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

Database Mail may be stopped in the msdb database. To check status of Database Mail, execute the following statement:

EXECUTE dbo.sysmail_help_status_sp;

To start Database Mail in a mail host database, run the following command in the msdb database:

EXECUTE dbo.sysmail_start_sp;

Service Broker examines the dialog lifetime for messages when it is activated; therefore, any messages that have been in the Service Broker transmission queue longer than the configured dialog lifetime immediately fails. Database Mail updates the status of failed messages in the sysmail_allitems and related views. You must decide whether to send the e-mail messages again. For more information about configuring the dialog lifetime that Database Mail uses, see sysmail_configure_sp (Transact-SQL).

See also