SQL Server:在 SQL Server 中配置数据库邮件,发送邮件
[作/译者]:鹏城万里 [日期]:-07-24 [来源]:本站原创 [查看]: 2031
【鹏城万里】 发表于
SQL Server:在 SQL Server 中配置数据库邮件。
对于真正的 DBA 来说,数据库邮件是必不可少的。 例如,数据库发生了警报(alert), DBA 希望得到邮件通知,以便即时排除故障。 或者是监控数据库作业(SQL Server Job)的运行状况,当检查到失败的作业时, 就发送数据库邮件报告给 DBA。
在 SQL Server 2000 中 配置 “SQL Mail”,需要安装 Outlook,配置过程比较麻烦。 在 SQL Server 中配置 “Database Mail” 就相对容易多了。 主要是理清思路。
SQL Server 并没有内置邮件服务器(Mail Server), 它跟我们发送邮件一样,需要用户名和密码通过 SMTP(Simple Message Transfer Protocol) 去连接邮件服务器。我们想让 SQL Server 来发送邮件,首先要告诉它用户名称,密码, 服务器地址,网络传送协议,邮件服务器的端口。。。等信息。这是通过 SQL Server 系统 存储过程 sysmail_add_account_sp 来实现的。
exec sysmail_add_account_sp
这样,在 SQL Server 中就添加了一个发送邮件的帐户。 道理上讲,有了这个邮件帐户,SQL Server 就可以发送邮件了。 如:
sp_send_dbmail @account_name = 'mail_account'
但是,SQL Server 考虑的更周全。试想:如果这个邮件帐户发生故障 (比如:用户密码过期,或者邮件服务器宕机)那岂不是发送不了邮件了? 为了应对这种情况,SQL Server 引入了 mail profile 这个东东。 一个 profile 中可以包含多个 account (邮件帐户),这样,SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户,如果发送成功,则退出, 否则,利用下一个邮件帐户发送邮件。其中,添加 profile 和 在 account 和 profile 建立映射是通过下面两个系统存储过程实现的:
sysmail_add_profile_sp
sysmail_add_profileaccount_sp
这时候,SQL Server 发送邮件,就采用下面的方法了:
sp_send_dbmail @profile_name = 'profile_name'
下面是具体的配置邮件步骤
在 sa 系统帐户下运行。
1. 启用 SQL Server 邮件功能。
use master
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Database mail XPs',1
go
reconfigure
go
2. 在 SQL Server 中添加邮件帐户(account)
exec msdb..sysmail_add_account_sp
@account_name = 'p.c.w.l' -- 邮件帐户名称(SQL Server 使用)
,@email_address = 'webmaster@' -- 发件人邮件地址
,@display_name = null -- 发件人姓名
,@replyto_address = null
,@description = null
,@mailserver_name = '58.215.64.159' -- 邮件服务器地址
,@mailserver_type = 'SMTP'-- 邮件协议(SQL 只支持 SMTP)
,@port= 25 -- 邮件服务器端口
,@username= 'webmaster@' -- 用户名
,@password= 'xxxxxxxxx'-- 密码
,@use_default_credentials = 0
,@enable_ssl = 0
,@account_id = null
3. 在 SQL Server 中添加 profile
exec msdb..sysmail_add_profile_sp @profile_name = 'dba_profile'-- profile 名称
,@description = 'dba mail profile' -- profile 描述
,@profile_id = null
4. 在 SQL Server 中映射 account 和 profile
exec msdb..sysmail_add_profileaccount_sp @profile_name = 'dba_profile' -- profile 名称
,@account_name = 'p.c.w.l'-- account 名称
,@sequence_number = 1 -- account 在 profile 中顺序
5. 利用 SQL Server Database Mail 功能发送邮件。
exec msdb..sp_send_dbmail @profile_name = 'dba_profile'-- profile 名称
,@recipients = 'sqlstudy@'-- 收件人邮箱
,@subject= 'SQL Server Mail Test' -- 邮件标题
,@body = 'Hello Mail!'-- 邮件内容
,@body_format = 'TEXT' -- 邮件格式
6. 查看邮件发送情况:
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 拥有发送权限。
现在,可以利用 SQL Server 发送数据库邮件了吧。如仍有问题,请留言。
本文由原创,版权所有,转载请注明作者和出处!
本文链接:/sql_article.php?id=072403