Enabling and configuring Database Mail in SQL Server using T-SQL

Database mail is the most useful feature of SQL Server which allows us to send email though the database. This feature can be used in reporting services, integration services and in our application also. By using this feature, we can easily tracks the email logs also and schedule these email alerts by the help of SQL Server Agent.

Well, we will learn how to enable and configure database mail in SQL Server using T-SQL codes without using Database Mail Configuration Wizard. 

So, we are dividing our T-SQL code into six steps which are executed in SQL one by one. 

These steps are defined step by steps as given below–

Step 1: Enable the Database Mail feature- We need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as given below:

USE [master] GO -- STEP 1: Enable Database Mail Extended Procedures – TSQL Option sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'Database Mail XPs',1 GO RECONFIGURE GO

Step 2: Create a profile for Database Mail- We need to create an email profile before creating an account. This can be done with the help of existing stored procedure called sysmail_add_profile_sp in msdb database as given below:

-- STEP 2: Create a New Mail Profile EXECUTE msdb.dbo.sysmail_add_profile_sp        @profile_name = 'DBANotifications',        @description = 'Profile for sending Automated DBA Notifications' GO

Step 3: Define the default profile – We have created the email profile in the system database. Now, we need to set this profile as the default as given below:

-- STEP 3: Set Profile as the default for all sql emails EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'DBANotifications',     @principal_name = 'public',     @is_default = 1 ; GO

Step 4:

Create an account for use with the profile – After creating the email profile, we need to create an account. As you know that this is the most important part of database mail configuration and it should require an authorized email id and smtp server with port number as given below:

-- STEP 4: Create An Account EXECUTE msdb.dbo.sysmail_add_account_sp     @account_name = 'SQLNotifications',     @description = 'Account for Automated DBA Notifications',     @email_address = 'email@domain.com',  -- Replace with your email is     @display_name = 'SQL Monitor',     @mailserver_name = 'smtp.domain.com'  -- Replace with your email smtp server GO

Step 5: Add the account to the profile – Now we have created profile and account for the database mail. We could have multiple profile and accounts in the database. So, we need to add the account to the email profiler as given below:

-- STEP 5: Create An Account EXECUTE msdb.dbo.sysmail_add_account_sp     @account_name = 'SQLNotifications',     @description = 'Account for Automated DBA Notifications',     @email_address = 'email@domain.com',  -- Replace with your email is     @display_name = 'SQL Monitor',     @mailserver_name = 'smtp.domain.com'  -- Replace with your email smtp server GO

Step 6: Send a test email to the DBAs email address – After adding account to profiler, we can send a test email as given below:

-- STEP 6: Send Email by the profiler EXEC msdb.dbo.sp_send_dbmail @profile_name='DBANotifications', @recipients='abc@your.com', @subject='Test message', @body='Congrates!!! Database Mail Received By you Successfully.'

T-SQL Code in a glance:

USE [master] GO -- STEP 1: Enable Database Mail Extended Procedures – TSQL Option sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'Database Mail XPs',1 GO RECONFIGURE GO -- STEP 2: Create a New Mail Profile EXECUTE msdb.dbo.sysmail_add_profile_sp        @profile_name = 'DBANotifications',        @description = 'Profile for sending Automated DBA Notifications' GO -- STEP 3: Set Profile as the default for all sql emails EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'DBANotifications',     @principal_name = 'public',     @is_default = 1 ; GO -- STEP 4: Create An Account EXECUTE msdb.dbo.sysmail_add_account_sp     @account_name = 'SQLNotifications',     @description = 'Account for Automated DBA Notifications',     @email_address = 'email@domain.com',  -- Replace with your email is     @display_name = 'SQL Monitor',     @mailserver_name = 'smtp.domain.com'  -- Replace with your email smtp server GO -- STEP 5: Add the Account to the Profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp     @profile_name = 'DBANotifications',     @account_name = 'SQLNotifications',     @sequence_number = 1 GO -- STEP 6: Send Email by the profiler EXEC msdb.dbo.sp_send_dbmail @profile_name='DBANotifications', @recipients='abc@your.com', @subject='Test message', @body='Congrats!!! database email is received by you successfully.'

Conclusion

To enable your database email through T-SQL codes, you should have the sysadmin rights. This code is very useful especially when you’re setting up Database Mail for multiple instances and multiple SMTP servers. You can create store procedure to send various emails from multiple SQL instances which makes your life easier to automate these email alerts.