Error Message [SQLSTATE 42000] (Error 50000):
Executed as user: DOMAIN\Account. 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’, search for ‘Database Mail XPs’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.
Solution:
You will receive the above-mentioned SQL error when you try to send a test email from the SQL Server and the Database mail feature is not enabled. The solution for the above error is pretty much clear from the error message itself, you need to enable the Database Mail feature and test to send email again.
How to enable database mail in SQL server
To enable the Database Mail XPs in SQL Server, execute the following T-SQL in SQL Server management studio (SSMS).
— Below code will show advanced options, it should be executed before executing Database Mail XPs code to enable it
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
— Execute the below code to enable the Database Mail in SQL Server
EXEC sp_configure ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO
— Confirm the changes by executing the below T-SQL
EXEC sp_configure ‘Database Mail XPs’
GO
— Execute the code to hide advanced options
EXEC sp_configure ‘show advanced options’, 0
GO
RECONFIGURE
GO