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
Conclusion
In this article, we share the solution to the SQL error (Error Message [SQLSTATE 42000] (Error 50000)) that DBA can face while working with the Database Mail. The solution is simple and easy. Mostly this error appears when DBA is working with the database mail for the first time as the database mail feature is disable by default and need to enable it before the configuration of DB Mail profile or sending test email.