SQL Server Database Mail Account Setup
What is Database Mail?
Database Mail is a feature in SQL Server that allows the engine to send email notifications to users. It’s commonly used for alerting DBAs or support teams when jobs fail, alerts are triggered, or reports are ready.
Unlike the older SQL Mail (which relied on MAPI), Database Mail is the recommended method because it’s more secure, flexible, and does not depend on a mail client like Outlook.
Purpose and Usage of Database Mail
- Send alerts for job success/failure
- Notify operators about system issues
- Distribute query results or reports via email
- Send status updates for long-running jobs or processes
How to Create a Database Mail Account
Below is a sample T-SQL script that:
- Enables Database Mail if not already enabled.
- Creates a new Database Mail account.
- Creates a profile and associates the account with the profile.
- Sets the newly created profile as the default.
When and Why to Use Database Mail
Database Mail is often used in the following scenarios:
- SQL Agent Job failures or completions
- Custom alerts for long-running queries or disk space issues
- Application-level notifications
- Automated report delivery
It offers a reliable and scalable method of email communication directly from your SQL Server environment.
Script:-
USE [master]
GO
sp_configure ‘show advanced options’, 1;
RECONFIGURE WITH OVERRIDE;
GO
sp_configure ‘Database Mail XPs’, 1;
RECONFIGURE;
GO– Step 2: Create a Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLDBAActivity’,
@description = ‘Profile for sending Automated DBA Notifications’;
GO– Step 3: Set the Profile as Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLDBAActivity’,
@principal_name = ‘public’,
@is_default = 1;
GO
— Step 4: Create a Mail Account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQLDBAActivity’,
@description = ‘Account for Automated DBA Notifications’,
@email_address = ‘*****DBA@*****.**’, — Replace with your email
@display_name = ‘SQL DBA Alert’,
@mailserver_name = ‘maildomin.*****.**’; — Replace with your SMTP server
GO
— Step 5: Link the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLDBAActivity’,
@account_name = ‘SQLDBAActivity’,
@sequence_number = 1;
GO
— Step 6: Restart the Database Mail Service
EXEC msdb.dbo.sysmail_stop_sp;
GO
EXEC msdb.dbo.sysmail_start_sp;
GO
— Step 7: Send a Test Email
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = ‘name@*****.**’, — Replace with recipient email
@subject = ‘Test Message generated from SQL Server DatabaseMail’,
@body = ‘This is a test message from SQL Server DatabaseMail’;
GO
PRINT ‘Mail account and profile configuration completed.’;
— To check the mail event log
SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;
Architecture Diagram of mail account creation
Best Practices
- Use a dedicated SMTP relay with limited access to improve security.
- Create separate profiles for different purposes (e.g., application alerts vs. DBA alerts)
