Configure SQL Mail XPs sys.xp_readmail by using sp_configure
SQL developers executing SQL Server SQL Mail XPs sys.xp_readmail can get the following error message if the sql database mail XPs is not configured or turned off by means of security.
In such a case where SQL Mail xp_readmail component is not enabled, following T-SQL statement is enough to throw the below sql error message
Msg 15281, Level 16, State 1, Procedure xp_readmail, Line 1
SQL Server blocked access to procedure 'sys.xp_readmail' of component 'SQL 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 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
SQL Server database administrators or SQL programmers who have enough authorizations on the SQL Server instance can configure SQL Mail and enable SQL Mail XPs by using the sp_configure system procedure as shown in the following steps.
First execute sp_configure sql procedure and check if advanced options are enabled or not.
If the output of the sp_configure command shows a long list of SQL Server configuration items, or the "show advanced options" item has the run_value column with value 1 then you can skip the following step.
If "show advanced options" is 0, then SQL Server database administrator should switch the configuration options from 0 to 1 as follows.
EXEC sp_configure 'show advanced options', 1
After the above step is executed successfully where you got the information message "Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.", we can continue.
Run the "EXEC sp_configure" command once more.
As you see in the above screenshot which shows partially the output of the sp_configure SQL Server configuration values, the SQL Mail XPs has run_value equal to 0
Now in this step we'll modify the run value of SQL database mail XPs from 0 to 1 using the following sp_configure command.
EXEC sp_configure 'SQL Mail XPs', 1
If you got the following information message from SQL Engine "Configuration option 'SQL Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install." this means you are successful to modify the SQL Server configuration values for database mail XPs.
Now you can revert back to original value of "show advanced options" configuration value and end the modification script here.
EXEC sp_configure 'show advanced options', 0
T-SQL developers can use the xp_readmail SQL Mail XPs in their sql scripts after this modification in the SQL Server configuration