Advanced SQL Server spDBMailSending
Complete Guide 2008–2025: Step-by-Step Configuration, Enhanced Stored Procedure with Attachments & Security, Testing, Dos/Don'ts, Alternatives & Business Scenarios
Database Mail Configuration (SQL Server 2008 – 2025)
Step-by-step guide for all supported versions
Database Mail is an enterprise solution for sending emails from the SQL Server Database Engine. It uses SMTP and is fully supported in SQL Server 2008 through 2025 with minor UI changes. Below are the steps to configure it using SQL Server Management Studio (SSMS).
In SSMS, connect to your SQL Server instance. Expand Management → right-click Database Mail → select Configure Database Mail. If it's the first time, you'll see a welcome screen; click Next.
Choose Set up Database Mail → Next. Enter a profile name (e.g., db_profiler) and click Add to create an SMTP account. Provide account name, email address, display name, reply email, SMTP server (e.g., smtp.office365.com), port (587), and authentication (Basic or Windows).
Choose whether the profile is Public or Private. For security, consider making it private and granting access to specific database users or roles. You can also set the profile as the default for the instance.
Set Account Retry Attempts, Retry Delay (seconds), Maximum File Size (Bytes), and Prohibited Attachment File Extensions. Defaults are usually fine, but adjust for your environment.
Review settings and click Finish. Once successful, you can send a test email. Use msdb.dbo.sp_send_dbmail with your profile name.
SQL Server 2008–2014 use similar SSMS wizards. SQL 2016+ have minor UI updates. SQL 2022/2025 support OAuth for Microsoft 365. Always check SMTP relay permissions.
SELECT * FROM msdb.dbo.sysmail_server and sysmail_profile to verify. Use sysmail_help_profile_sp and sysmail_help_account_sp for details.
Stored Procedure Parts Explained
Understanding each component of spDBMailSending
| Part | Description |
|---|---|
| Parameters | 15+ parameters allow full customization: recipient lists, subject, body, user credentials, attachment options, and logging metadata. |
| Session ID | Generated from current timestamp and a GUID fragment, ensuring uniqueness for audit and correlation. |
| Validation | Checks required fields (@Usr, @key, @toEmails, @Sub), validates user existence and password against MailUsers table, and rejects inactive users. |
| Attachment Handling | Validates attachment type (CSV/EXCEL), filename sanitization (removes invalid Windows chars), database existence, and query type (SELECT/WITH/EXEC). |
| Email Sending | Uses msdb.dbo.sp_send_dbmail with profile 'db_mailer', HTML body, HIGH importance, Confidential sensitivity, and query attachment if required. |
| Logging | On success, inserts into DBMailLogs; on failure, captures error details and parameter dump into ErrorLogs, then returns structured error info. |
The procedure is designed to be robust, secure, and production-ready. It combines user authentication, query safety, filename sanitization, and comprehensive logging – all essential for automated email workflows.
Enhanced Stored Procedure (Complete)
Advanced features: dynamic profile, file path attachment, better validation, and logging
The following script enhances the original with: dynamic mail profile, file path attachment support, table variable for error logging, query timeout control, and improved parameter validation. Copy and adapt as needed.
-- ============================================================ -- ENHANCED STORED PROCEDURE: log.spDBMailSending_Enhanced -- Compatible: SQL Server 2008 – 2025 -- Features: dynamic profile, file path attachment, query timeout, -- improved logging, and parameter validation -- ============================================================ USE [DBName] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [log].[spDBMailSending_Enhanced] ( @SrcSrv VARCHAR(250) = NULL, @AppName VARCHAR(250) = NULL, @DBName VARCHAR(250) = NULL, @Sub NVARCHAR(2500) = NULL, @toEmails VARCHAR(2500) = NULL, @toCCs VARCHAR(2500) = NULL, @toBCCs VARCHAR(2500) = NULL, @bdy NVARCHAR(MAX) = NULL, @Usr VARCHAR(250) = NULL, @key VARCHAR(250) = NULL, @IP VARCHAR(350) = NULL, @AttachmentQuery NVARCHAR(MAX) = NULL, @AttachmentFileName VARCHAR(500) = NULL, @AttachmentType VARCHAR(20) = 'CSV', @QuerySeparator VARCHAR(10) = ',', @IncludeHeader BIT = 1, @QueryResultWidth INT = 32767, @HasAttachment BIT = 0, @MailProfile VARCHAR(250) = 'db_mailer', -- dynamic profile @FilePath VARCHAR(500) = NULL, -- existing file attachment @QueryTimeout INT = 120 -- seconds for query execution ) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @SessionID VARCHAR(50), @ExecuteDatabase SYSNAME, @CleanQuery NVARCHAR(MAX), @OriginalFileName VARCHAR(500), @Extension VARCHAR(20), @Now DATETIME = GETDATE(), @FinalAttachmentFileName VARCHAR(500); SET @SessionID = CONVERT(VARCHAR(8), @Now, 112) + REPLACE(CONVERT(VARCHAR(8), @Now, 108), ':', '') + RIGHT('000' + CONVERT(VARCHAR(3), DATEPART(MILLISECOND, @Now)), 3) + RIGHT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), 8); BEGIN TRY -- Basic validation (same as original) IF NULLIF(LTRIM(RTRIM(@Usr)), '') IS NULL BEGIN SELECT 'Invalid User' AS Sts, @SessionID AS SessionID; RETURN; END IF NULLIF(LTRIM(RTRIM(@key)), '') IS NULL BEGIN SELECT 'Invalid Key' AS Sts, @SessionID AS SessionID; RETURN; END IF NULLIF(LTRIM(RTRIM(@toEmails)), '') IS NULL BEGIN SELECT 'Recipient email is required' AS Sts, @SessionID AS SessionID; RETURN; END IF NULLIF(LTRIM(RTRIM(@Sub)), '') IS NULL BEGIN SELECT 'Email subject is required' AS Sts, @SessionID AS SessionID; RETURN; END -- User authentication IF NOT EXISTS (SELECT 1 FROM [log].[MailUsers] WHERE MailUsr = @Usr AND IsInactive = 0) BEGIN SELECT 'Invalid User' AS Sts, @SessionID AS SessionID; RETURN; END IF NOT EXISTS (SELECT 1 FROM [log].[MailUsers] WHERE MailUsr = @Usr AND pwd = @key AND IsInactive = 0) BEGIN SELECT 'Invalid Key' AS Sts, @SessionID AS SessionID; RETURN; END -- Attachment handling (enhanced: file path support) IF @FilePath IS NOT NULL AND LEN(LTRIM(RTRIM(@FilePath))) > 0 BEGIN -- Validate file exists (using xp_fileexist or sys.dm_os_file_exists if 2016+) IF OBJECT_ID('tempdb..#FileCheck') IS NOT NULL DROP TABLE #FileCheck; CREATE TABLE #FileCheck (FileExists INT, IsDirectory INT, ParentDirExists INT); INSERT INTO #FileCheck EXEC master.sys.xp_fileexist @FilePath; IF NOT EXISTS (SELECT 1 FROM #FileCheck WHERE FileExists = 1) BEGIN SELECT 'File not found' AS Sts, @SessionID AS SessionID; RETURN; END END -- If using query attachment, validate as before (not shown fully for brevity but same logic) -- ... (rest of original logic) ... -- Send email with dynamic profile and optional file path attachment IF @HasAttachment = 1 OR @FilePath IS NOT NULL BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile, @recipients = @toEmails, @copy_recipients = @toCCs, @blind_copy_recipients = @toBCCs, @subject = @Sub, @body = @bdy, @body_format = 'HTML', @importance = 'HIGH', @sensitivity = 'Confidential', @exclude_query_output = 1, @append_query_error = 1, @execute_query_database = @ExecuteDatabase, @query = @AttachmentQuery, @attach_query_result_as_file = 1, @query_attachment_filename = @AttachmentFileName, @query_result_header = @IncludeHeader, @query_result_separator = @QuerySeparator, @query_result_no_padding = 1, @query_result_width = @QueryResultWidth, @file_attachments = @FilePath, -- existing file @query_timeout = @QueryTimeout; END ELSE BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile, @recipients = @toEmails, @copy_recipients = @toCCs, @blind_copy_recipients = @toBCCs, @subject = @Sub, @body = @bdy, @body_format = 'HTML', @importance = 'HIGH', @sensitivity = 'Confidential', @exclude_query_output = 1, @append_query_error = 1; END -- Log success (same as original) INSERT INTO [log].[DBMailLogs] ([SrcSrv],[AppName],[DBName],[Sub],[toEmails],[toCCs],[toBCCs],[Usr],[DateAdded],[IP],[SessionID]) VALUES (@SrcSrv, @AppName, @DBName, @Sub, @toEmails, @toCCs, @toBCCs, @Usr, GETDATE(), @IP, @SessionID); SELECT 'Emailed' AS Sts, @SessionID AS SessionID, CASE WHEN @HasAttachment = 1 THEN @AttachmentFileName ELSE NULL END AS AttachmentFileName; END TRY BEGIN CATCH -- Enhanced error handling (same as original but with @MailProfile added) DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProc NVARCHAR(256), @ParamDump NVARCHAR(MAX); SELECT @ErrorMessage=ERROR_MESSAGE(), @ErrorNumber=ERROR_NUMBER(), @ErrorSeverity=ERROR_SEVERITY(), @ErrorState=ERROR_STATE(), @ErrorLine=ERROR_LINE(), @ErrorProc=ERROR_PROCEDURE(); SET @ParamDump = CONCAT('User: ',ISNULL(@Usr,''),', To: ',ISNULL(@toEmails,''),', Profile: ',ISNULL(@MailProfile,''),', SessionID: ',ISNULL(@SessionID,'')); BEGIN TRY INSERT INTO [log].[ErrorLogs] (ProcName,ErrorMessage,ErrorNumber,ErrorSeverity,ErrorState,ErrorLine,ParamDump,LoggedBy,AppName,IP) VALUES ('spDBMailSending_Enhanced', @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine, @ParamDump, @Usr, @AppName, @IP); END TRY BEGIN CATCH -- suppress END CATCH SELECT 'Error' AS Sts, @ErrorNumber AS ErrorNumber, @ErrorMessage AS ErrorMessage, @SessionID AS SessionID; END CATCH END
Note: The enhanced script adds @MailProfile (allow different profiles), @FilePath (attach existing file), and @QueryTimeout. The full logic for attachment query validation remains similar to the original; only key additions are shown for brevity.
Testing Database Mail & spDBMailSending
Step-by-step verification methods
After configuration, right-click Database Mail → Send Test E-Mail. Enter recipient and check inbox. This verifies SMTP settings and profile.
Execute EXEC msdb.dbo.sp_send_dbmail @profile_name='db_Mailer', @recipients='you@email.com', @subject='Test', @body='Hello'; Check result and mail log.
Use @query='SELECT TOP 10 * FROM sys.tables' with @attach_query_result_as_file=1. Verify attachment.
Query msdb.dbo.sysmail_allitems, sysmail_sentitems, sysmail_faileditems, and sysmail_log for errors.
Call with valid user/key. Try invalid parameters to ensure validation works. Check DBMailLogs and ErrorLogs.
sysmail_help_status_sp to check if Database Mail is started.
Dos and Don'ts
Best practices for production use
Create a profile specifically for your application, e.g., 'App_Reports'. Avoid using default profiles.
Always sanitize filenames and validate query types to prevent injection and file system errors.
Regularly query sysmail_log and your custom ErrorLogs for failed sends and performance issues.
Avoid sending files > 10 MB via Database Mail; use file share links instead to prevent blocking.
Use encryption or certificate-based authentication for SMTP, not plain text in scripts.
Only grant execute on spDBMailSending to authorized roles; never to public.
Pros and Cons of spDBMailSending
When to use and when to avoid
• Centralized email logic
• Robust validation and logging
• CSV/Excel attachment support
• Session tracking for audits
• Works across SQL versions
• Adds overhead vs raw sp_send_dbmail
• Requires MailUsers table setup
• Attachment size limited by SMTP
• Not suitable for high-frequency mass emails
Alternatives
Other methods for SQL Server email automation
Use the built-in procedure directly when you don't need extra validation or logging.
Integrate email in ETL packages with attachments and HTML formatting.
Use PowerShell scripts to query SQL and send via SMTP client, offering more flexibility.
For cloud-based or hybrid scenarios, use Azure services to send emails and alerts.
Business Usage Scenarios
Real-world applications of spDBMailSending
Send automated CSV or Excel summary of daily sales to management.
Notify DBAs when job failures, deadlocks, or performance thresholds are exceeded.
Send reorder alerts when stock levels fall below minimum.
Send password reset emails, account confirmations, or activity summaries.
Distribute audit or regulatory reports on a schedule.
Notify when data loads succeed or fail, with attached error details.
Frequently Asked Questions
Common queries about spDBMailSending
FreeLearning365 Resources
Explore more free tools, tutorials, and learning materials
0 Comments
thanks for your comments!