MSSQL发送邮件

SQL Server 并没有内置邮件服务器（Mail Server），它跟我们发送邮件一样，需要用户名和密码通过 SMTP（Simple Message Transfer Protocol）去连接邮件服务器。我们想让 SQL Server 来发送邮件，首先要告诉它用户名称，密码，服务器地址，网络传送协议，邮件服务器的端口……等信息。

以下脚本实现了数据库邮件的配置：

下面是具体的配置邮件步骤 

在 sa 系统帐户下运行。 

 

-- 打开邮件设置
use master
go
exec sp_configure 'show advanced options',1    --以下示例显示如何设置并列出所有的配置选项。先将 show advanced option 设置为 1，便可显示高级配置选项。更改该选项后，不带参数执行 sp_configure 将会显示全部配置选项。 
go
reconfigure
go
exec sp_configure 'Database mail XPs',1    
go
reconfigure
go

-- 添加邮件账户
exec msdb..sysmail_add_account_sp                                            --创建一个新的数据库邮件帐户，用于保存有关 SMTP 帐户的信息
    @account_name='yyy'                                                        --邮件账户名称(SQL Server 使用)
    ,@email_address='yyy753@qq.com'                                        --发件人邮件地址
    ,@display_name=null                                                    --发件人姓名
    ,@replyto_address=null                                                    --回复该帐户的邮件时使用的地址
    ,@description=null                                                        --对帐户的说明
    ,@mailserver_name='smtp.qq.com'                                            --邮件服务器地址
    ,@mailserver_type='SMTP'                                                --邮件协议
    ,@port=25                                                                --邮件服务器端口
    ,@username='yyy@qq.com'                                            --用户名
    ,@password='123456'                                                --密码
    ,@use_default_credentials=0                                                --指定是否使用 SQL Server 数据库引擎的凭据将邮件发送到 SMTP 服务器
    ,@enable_ssl=0                                                            --指定数据库邮件是否使用安全套接字层对通信进行加密
    ,@account_id=null                                                        --返回新帐户的帐户 ID
    
-- 添加profile
exec msdb..sysmail_add_profile_sp        --创建新的数据库邮件配置文件。
    @profile_name='dba_profile'            --新配置文件的名称
    ,@description='dba mail profile'    --新配置文件的说明
    ,@profile_id=null                    --返回新配置文件的 ID

-- 映射account和profile
exec msdb..sysmail_add_profileaccount_sp    --在数据库邮件配置文件中添加一个数据库邮件帐户
    @profile_name='dba_profile'                --要在其中添加帐户的配置文件的名称
    ,@account_name='yyy'                    --要在配置文件中添加的帐户的名称
    ,@sequence_number=1                        --配置文件中的帐户的序列号
    
-- 利用Database Mail功能发送邮件
exec msdb..sp_send_dbmail                                                                --向指定收件人发送电子邮件
    @profile_name='dba_profile'                                                            --发送邮件的配置文件的名称
    ,@recipients='yangyunyu123@foxmail.com'                                                --收件人
    ,@subject='SQL Server E-Mail Test!'                                                    --邮件标题
    ,@body='Hello!yyy！'                            --邮件内容
    ,@body_format='TEXT'                                                                --邮件格式

-- 查看发邮件结果    
use msdb
go
select * from sysmail_allitems
select * from sysmail_mailitems
select * from sysmail_event_log


--如果不是以 sa 帐户发送邮件，则可能会出现错误： 

--Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
--EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

--这是因为，当前 SQL Server 登陆帐户（login），在 msdb 数据库中没有发送数据库邮件的权限，需要加入 msdb 数据库用户，并通过加入 sp_addrolemember 角色赋予权限。假设该SQL Server 登陆帐户名字为 “dba” 

--use msdb
--go
--create user dba for login dba
--go
--exec dbo.sp_addrolemember @rolename   = 'DatabaseMailUserRole',
--                          @membername = 'dba'
--go
--此时，再次发送数据库邮件，仍可能有错误： 
--Msg 14607, Level 16, State 1, Procedure sp_send_dbmail, Line 119
--profile name is not valid

--虽然，数据库用户 “dba” 已经在 msdb 中拥有发送邮件的权限了，但这还不够，他还需要有使用 profile：“dba_profile” 的权限。 

--use msdb
--go
--exec sysmail_add_principalprofile_sp  @principal_name = 'dba'
--                                     ,@profile_name   = 'dba_profile'
--                                     ,@is_default     = 1

--从上面的参数 @is_default=1 可以看出，一个数据库用户可以在多个 mail profile 拥有发送权限。