HOWTO: configure database mail with MSSQL

Some developers may choose to utilize SQL’s Mail option as in some scenarios there may be different front end type web applications tied to a single type of database (in this case MS SQL 2005).

This prevents the developer from having to write the same email method for each type of front end application.

This particular method may also be referenced as sending email from the "back end".

NOTE: Supported on MS SQL 2005 and up ONLY.

Before you continue you must obtain the following information from the customer:

-Database Name.
-DB user.
-Mail Account login (User, password, domain).

Example info-

DB Name: Caneja.com
DB User: caneja

Mail server: mail.caneja.com
Mail User: sql@caneja.com
Mail password: sql

Replace any variables referencing the caneja.com or caneja account with the information provided by the customer then paste the script in a new query window in SQL Management Studio while logged in as an admin.

This operation must be performed on the server where the db is hosted.

You may execute the script at once and it should output a "Command(s) completed successfully".

================================================

— Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘caneja.com’,
@description = ‘caneja.com’,
@email_address = ‘sql@caneja.com‘,
@replyto_address = ‘sql@caneja.com‘,
@display_name = ‘sql@caneja.com‘,
@mailserver_name = ‘mail.caneja.com’,
@username =’sql@caneja.com‘,
@password =’sql’ ;

— Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘caneja.com’,
@description = ‘caneja.com’ ;

— Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘caneja.com’,
@account_name = ‘caneja.com’,
@sequence_number =1 ;

exec msdb.dbo.sysmail_help_account_sp

–adding user to msdb db.
use msdb
go
sp_adduser ‘caneja’
go
sp_addrolemember ‘DatabaseMailUserRole’,’caneja’
GO

— Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘caneja.com’,
@principal_name = ‘caneja’,
@is_default = 1 ;