Troubleshooting SQL Server blocked access to procedure sp_send_dbmail

Use sp_send_dbmail to send email from SQL Server | How to Use Database Email SQL Server Feature

Troubleshooting SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server.

How to send email from SQL Server | Configure Database Mail SQL Server

While trying to execute msdb..sp_send_dbmail system procedure to test email functionality so that it can be incorporated within an SQL Server Agent Job to send success messages whenever Full Database Backup has completed successfully. However, the execution of sp_send_dbmail stored procedure failed with the below mentioned error. In this article we will take a look at the steps you need to follow to resolve this issue of Database Mail SQL Server. The resolution steps are applicable to SQL Server 2005 and higher versions.

Error Message related to Database Email SQL Server

Executed as user: Domain\Username. SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

One would always want to configure Database Mail SQL Server (DBMail SQL Server) to get notified on important notifications. SQL Email is a simple feature to enable. One Database Mail feature is enabled and Database Mail is configured correctly sending mail messages from SQL Server becomes easy. You could use the TSQL Send Email script mentioned in this tip to send SQL Email.

Send Email using sp_send_dbmail stored procedure available in MSDB database

Execute the below mentioned TSQL code to send email using sp_send_dbmail stored procedure (send mail from sql server) after a task completion.

/* Script to send email sql server | send email from sql server */ 
EXEC msdb..sp_send_dbmail
@profile_name = 'EMailProfile', 
@recipients = 'user@domain.com',
@body = 'Success - Full Database Backup',
@subject = 'Full Database Backup of all the database is completed successfully.'

Learn how to configure database mail SQL Server to send mail from SQL Server. You would sometimes want to be notified after a database backup. This tip explains how to send email from SQL Server.

How to Fix sp_send_dbmail mail issues in SQL Server

In order to resolve this issue a database administrator can connect to SQL Server Instance with System Administrator (SA) Privileges and execute the below mentioned TSQL Statement to enable Database Mail XPs feature of SQL Server. Once enabled SQL Send EMail Feature in SQL Server using sp_configure stored procedure then only you will be able to use SQL Server send email from stored procedure functionality.

/* Enable SQL Send EMail Feature in SQL Server using sp_configure advanced options | Send Mail SQL Server | Send Mail in SQL */

USE MASTER
GO

SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

/* Enable Database Mail XPs Advanced Options in SQL Server */
SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO

SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

In the above TSQL code snippet you could see that once the Database Mail XPs feature is enabled we have gone ahead and disabled Show Advanced Options of SP_CONFIGURE system stored procedure.

Related Articles

• How to Backup Database in SQL Server
• How to Restore Database in SQL Server
• Performance Dashboard Reports in SQL Server 2012
• Tips to Avoid Account Lockout Issues
• Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
• Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
• Why an SQL Server Database from a higher version cannot be restored onto a lower version?
• How to identify if the database was upgraded from a previous version of SQL Server
• Using Transparent Data Encryption Feature of SQL Server 2008
• Configuring Database Instant File Initialization Feature of SQL Server
• Changing the default location of SQL Server Data and Log files
• Changing Default SQL Server Backup Folder in SQL Server 2008
• How to repair a Suspect Database in SQL Server
• Steps to Rebuild System Databases in SQL Server
• How to Get Exclusive Access to SQL Server Database