SQL Server 以 sp_send_dbmail 發送郵件完整設定教學

SQL Server 內建 msdb.dbo.sp_send_dbmail 預存程序，配合 Database Mail 功能，可直接從 T-SQL 發送郵件通知、報表、警示。本文以圖文並茂方式，完整記錄從建立郵件設定檔、SMTP 帳戶、啟用功能到發送測試的全流程。

適用版本

• SQL Server 2008 R2 及以上版本（含 2012/2014/2016/2017/2019/2022）
• 需具備 sysadmin 固定伺服器角色權限

一、建立 Database Mail 設定檔與帳戶（SSMS 圖形化操作）

1. SSMS 連線執行個體 → 展開 管理 → 右鍵 Database Mail → 設定 Database Mail
2. 選擇 設定 Database Mail：啟用、建立設定檔與帳戶 → 下一步
3. 新增設定檔：輸入設定檔名稱（如 db_mail_profile）、描述 → 新增...
4. 新增帳戶 填寫 SMTP 參數：

5. 完成帳戶新增 → 將帳戶加入設定檔 → 下一步 → 完成精靈
6. 系統詢問是否啟用 Database Mail，選 是

二、以 T-SQL 啟用進階選項與 Database Mail（可選，精靈已自動處理）

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

三、發送測試郵件

在 msdb 資料庫執行：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail_profile',   -- 步驟1建立的設定檔名稱
    @recipients = 'admin@company.com',   -- 收件者，多個用分號分隔
    @subject = 'SQL Server 測試郵件',
    @body = '這是從 SQL Server sp_send_dbmail 發送的測試郵件。',
    @body_format = 'TEXT';               -- 或 'HTML'

四、進階參數與常用選項

五、帶查詢結果附件範例（每日銷售報表）

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail_profile',
    @recipients = 'manager@company.com',
    @subject = '每日銷售報表 ' + CONVERT(varchar(10), GETDATE(), 120),
    @body = '附件為今日銷售明細，請查收。',
    @query = 'SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Sales.Orders WHERE OrderDate = CAST(GETDATE() AS DATE)',
    @execute_query_database = 'ERP_DB',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'DailySales_' + CONVERT(varchar(8), GETDATE(), 112) + '.csv',
    @query_result_separator = ',',
    @query_result_header = 1;

六、郵件狀態追蹤與疑難排解

發送後可查詢以下系統檢視表：

• msdb.dbo.sysmail_allitems：所有郵件項目
• msdb.dbo.sysmail_sentitems：已發送（sent_status = 1）
• msdb.dbo.sysmail_unsentitems：未發送（sent_status = 3）
• msdb.dbo.sysmail_faileditems：發送失敗（sent_status = 2）
• msdb.dbo.sysmail_log：詳細錯誤日誌

常見失敗原因：

1. SMTP 認證失敗（密碼錯誤、未開啟應用程式密碼、防火牆阻擋 587/465 埠）
2. Database Mail XPs 未啟用
3. 設定檔名稱拼寫錯誤
4. 附件路徑在 SQL Server 服務帳號無讀取權限

七、安全性與最佳實踐

• 建立專用郵件發送帳號，避免使用管理員個人信箱
• 使用應用程式密碼（Gmail、Outlook 等現代認證環境必備）
• 限制 EXECUTE 權限僅授予必要角色/使用者：GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO [AppRole];
• 設定 @importance、@sensitivity 協助收件端分類
• 定期清理 sysmail_* 歷史記錄避免 msdb 膨脹

完成上述設定，即可在預存程序、Agent 作業、觸發器中直接呼叫 sp_send_dbmail 實現自動化郵件通知，大幅提升營運監控與業務流程的即時觸達能力。