如何在SQL Server中配置数据库邮件

Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.

正如您所希望的那样，数据库邮件是一种从SQL Server数据库引擎向用户发送电子邮件的解决方案。 使用数据库邮件，数据库应用程序可以发送电子邮件，例如，可以包含查询结果或仅向用户警告数据库中发生的事件。

The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:

数据库邮件配置过程包括三个主要步骤。 为了成功完成此操作，我们需要：

• create a Database Mail account,
  创建一个数据库邮件帐户，
• create a Database Mail profile,
  创建一个数据库邮件配置文件，
• and configure those two to work together
  并配置这两个一起工作

配置数据库邮件 (Configuring Database Mail)

To create a Database Mail profile, we can use either the Database Mail Configuration Wizard or T-SQL code. Using Configuration Wizard is easier, but bear in mind that Database Mail is turned off in SQL Server Express editions.

要创建数据库邮件配置文件，我们可以使用数据库邮件配置向导或T-SQL代码。 使用配置向导更容易，但是请记住， SQL Server Express版本中的数据库邮件已关闭。

Usually, all we need to do is go in Object Explorer, connect to the SQL Server instance we want to configure Database Mail on and expand the server tree. Then expand the Management node and double-click Database Mail or right-click and choose Configure Database Mail to open the Database Mail Configuration Wizard:

通常，我们所需要做的就是进入对象资源管理器，连接到我们要在其上配置数据库邮件SQL Server实例，并展开服务器树。 然后展开“管理”节点，双击“数据库邮件”，或右键单击并选择“ 配置数据库邮件”以打开“数据库邮件配置向导”：

Since Microsoft SQL Server 2016 Express edition is used in this article as an example, the Management node does not have Database Mail:

由于本文以Microsoft SQL Server 2016 Express版本为例，因此“管理”节点没有数据库邮件：

This doesn’t mean we cannot use it because it’s only not available as an interface, but it’s still available in the SQL Server Database Engine itself. We just need to enable it using T-SQL.

这并不意味着我们不能使用它，因为它不仅不能用作接口，而且在SQL Server数据库引擎本身中仍然可用。 我们只需要使用T-SQL启用它即可。

To enable Database Mail, run the following code:

要启用数据库邮件，请运行以下代码：

 
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
 

In this case, running the code triggered an error:

在这种情况下，运行代码会触发错误：

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0] The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.

消息15123，级别16，状态1，过程sp_configure，第62行[批处理开始第0行]配置选项'Database Mail XPs'不存在，或者它可能是高级选项。

This is going to happen from time to time because this is an advanced option. To fix this, we need to change the show advanced options default value from 0 to 1.

由于这是高级选项，因此会不时发生。 要解决此问题，我们需要将显示高级选项的默认值从0更改为1。

To do this run the following code:

为此，请运行以下代码：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
 
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

This time, the query is executed successfully. Once these two options are changed from ‘0’ to ‘1’, Database Mail is turned on:

这次，查询成功执行。 将这两个选项从“ 0”更改为“ 1”后，数据库邮件将打开：

Now we can get back to setting up the e-mail profile and adding an e-mail account. To do this we will use some stored procedures in msdb database.

现在，我们可以回到设置电子邮件配置文件并添加电子邮件帐户的方式。 为此，我们将在msdb数据库中使用一些存储过程。

To create a new Database Mail profile named ‘Notifications’ we will use the sysmail_add_profile_sp stored procedure and the following code:

要创建名为“ Notifications”的新数据库邮件配置文件，我们将使用sysmail_add_profile_sp存储过程和以下代码：

-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Notifications',  
    @description = 'Profile used for sending outgoing notifications using Gmail.' ;  
GO

To grant permission for a database user or role to use this Database Mail profile, we will use the sysmail_add_principalprofile_sp stored procedure and the following code:

要授予数据库用户或角色使用此数据库邮件配置文件的权限，我们将使用sysmail_add_principalprofile_sp存储过程和以下代码：

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'Notifications',  
    @principal_name = 'public',  
    @is_default = 1 ;
GO

To create a new Database Mail account holding information about an SMTP account, we will use the sysmail_add_account_sp stored procedure and the following code:

要创建一个新的数据库邮件帐户，其中包含有关SMTP帐户的信息，我们将使用sysmail_add_account_sp存储过程和以下代码：

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'Gmail',  
    @description = 'Mail account for sending outgoing notifications.',  
    @email_address = 'Use a valid e-mail address',  
    @display_name = 'Automated Mailer',  
    @mailserver_name = 'smtp.gmail.com',
    @port = 465,
    @enable_ssl = 1,
    @username = 'Use a valid e-mail address',
    @password = 'Use the password for the e-mail account above' ;  
GO

To add the Database Mail account to the Database Mail profile, we will use the sysmail_add_profileaccount_sp stored procedure and the following code:

要将数据库邮件帐户添加到数据库邮件配置文件，我们将使用sysmail_add_profileaccount_sp存储过程和以下代码：

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Notifications',  
    @account_name = 'Gmail',  
    @sequence_number =1 ;  
GO

Execute the code from all stored procedures, and you should get the message that the whole code is executed successfully:

从所有存储过程中执行代码，您应该得到一条消息，说明整个代码已成功执行：

<