Interview Q&A
Hands-On Labs
Previous Tutorial
13
SepSQL Server Database Mail Setup
24 Mar 2024
Advanced
6.48K Views
4 min read
Learn via Video Course & by Doing Hands-on Labs
SQL Server Course
SQL Server Database Mail Setup: An Overview
As part of my SQL Server Tutorial, I just set up database mail in SQL Server 2008. Here, I'll explain what I've learned and how I configured this powerful capability for sending mail in SQL Server 2008, which is required for anyone taking a SQL Server Certification Course.Database Mail in SQL Server
The database mail feature was introduced in SQL Server 2005 by Microsoft. This feature is also available in SQL Server 2008 and 2012. Before Database mail, we have SQL Mail feature in Sql Server 2000.
Database Mail is more reliable, secure, and faster than SQL Mail in SQL Server 2000. SQL Mail is based on MAPI (Messaging Application Programming Interface) whereas Database Mail is based on SMTP (Simple Mail Transfer Protocol. Moreover, database mail uses a service broker service and this service needs to be enabled for Database Mail.
By default, SQL Database mail is not enabled. We can enable this feature by using a system-defined stored procedure, configuration manager, or Database Mail Wizard. I am sharing both the tricks to enable this feature.
Steps for SQL Server Database Mail Setup
We can configure SQL Database mail in the following steps by using the Database mail wizard as shown below.
1. Create a Profile and Account
In the first step, we will create a profile and account by using the Configure Database Mail Wizard as shown below.
A profile can have multiple email accounts. It can be of two types.
1. Public Profile
A public profile can be accessed by any users and these users will have the ability to send emails.
2. Private Profile
A private profile is only accessed by granted users and only these users can send emails.
2. Configure Database Mail
After successfully creating of Profile and Account, we will configure the Database Mail using the system-defined stored procedure “sp_configure ” as shown below.
GO
sp_CONFIGURE Database Mail XPs', 1
GO
RECONFIGURE 3. Send Test Mail
We can send test mail by using the wizard and T-SQL statement as shown below.
Using Wizard
Using T-SQL Statement
USE msdb
GO
EXEC sp_send_dbmail @profile_name='Shailendra Chauhan Profile', @recipients='shailendra@ymail.com', @subject='Database Mail Test', @body= ‘This is a test e-mail sent from Database Mail' 4. Check Your Inbox
After sending test mail, you need to check the mail received in your inbox. I received the mail "Database Mail Test" in my inbox as shown below:
Read More
Summary
In this article, I try to explain how to set up SQL Database Mail in SQL Server with an example. I hope after reading this article you will know how to configure SQL Database Mail in SQL Server. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.
FAQs
Q1. How do I configure SQL Server Database Mail?
To set up Database Mail, open SQL Server Management Studio and create a mail profile and account, specifying the mail server, authentication, and sender information.
Q2. Can Database Mail be used with many SQL Server instances?
Yes, Database Mail may be configured to function with numerous SQL Server instances by creating individual profiles and accounts for each instance.
Q3. What permissions are required to configure Database Mail?
To configure Database Mail, you must be a member of the sysadmin fixed server role or the DatabaseMailUserRole in the msdb database.
Q4. How can I check if Database Mail is working properly?
To test the functioning of Database Mail, send a test email from SQL Server Management Studio or use a T-SQL script to send a test message programmatically.
Take our Sqlserver skill challenge to evaluate yourself!
In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.
