SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

Troubleshooting SQL Server blocked access to procedure sp_send_dbmail

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

Introduction

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 MSDB..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. The resolution steps are applicable to SQL Server 2005 and higher versions.

Error Message

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.

Related Articles

Send Email using SP_SEND_DBMAIL system stored procedure available in MSDB database

Execute the below mentioned TSQL code to send email using SP_SEND_DBMAIL Stored Procedure.

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.'

Resolution

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.

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.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup!



Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips