Configure and Send Database Email in SQL Server 2012 Express Edition

Configure and Send Database Email in SQL Server 2012 Express Edition

Hello and welcome, lets discuss how to Configure and Send Database Email in SQL Server 2012 Express Edition. One of the main difference in SQL Server Express Edition is there is no GUI setup wizard still we can configure database mail from TSQL script and use it.

lets see how Enterprise Edition object explorer looks like with Database Mail option enable in it.

1sql

lets see how Express Edition object explorer looks like with No Database Mail option.

2sql

Lets see step by step process to setup and enable database email.

To Set up a Database email, we need:

  • A Sysmail Account
  • A Database Profile
  • Adding Database Email Account to Profile
  • Grants permission

next, executing send email stored procedure.

Lets start with A SysMail Account.

To add a SysMail account SQL Server has an inbuilt system stored procedure named ‘sysmail_add_account_sp‘. If you want to explore more about read here.

/*
Change the parameters values as per your need.
*/

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using SQL Server',
@email_address = '[email protected]',
@display_name = 'SQL Server',
@username='[email protected]',
@password='your_password',
@mailserver_name = 'your_email_server_name'

Database Profile

Now, we have our SysMail Account ready we shall add Database profile to it. To Create Database Profile we have inbuilt stored procedure named ‘sysmail_add_profile_sp‘. If you want to explore more about read here.

/*
Change your parameter values accordingly
*/

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailTest',
@description = 'Profile used to send mail'

Adding Database Email Account to Profile

We now, need to assign/add email account to our profile to do so, we have inbuilt stored procedure named ‘sysmail_add_profileaccount_sp‘. If you want to explore more about read here.

/*
Change your parameter values accordingly
*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1

Grants permission

Grant  permission for a database user or role to use a Database Mail profile.

To Grants permission for a database user or role to use a Database Mail profile use ‘sysmail_add_principalprofile_sp‘.
/*
Change your parameter values accordingly
*/
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@profile_name = 'MailTest', 
@principal_name = 'public', 
@is_default = 1 ;

/* A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.
*/

Thats all…

Send an email through TSQL script:

/*
Send Mail using Created Profile
*/

exec msdb.dbo.sp_send_dbmail 
@profile_name = 'MailTest', 
@recipients = '[email protected]', 
@subject = 'Mail Test', 
@body = 'Mail Sent Successfully', 
@body_format = 'text'

If all is well, after executing the above script you should receive an email.

In some scenarios you may get ‘Database Mail XPs‘ errors, and error looks like

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, see “Surface Area Configuration” in SQL Server Books Online.
to fix this,
/*
Run the below script to fix Database Mail XP's errors
*/

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

Thanks and Regards.

Leave a Reply

Your email address will not be published. Required fields are marked *