ADVANCED USERS ONLY - NOT COVERED UNDER WIMS SUPPORT AGGREEMENT
Discuss with IT / DBA before attempting to setup.
Setup SQL Server Express Mail (may not be necessary when running non-express versions of SQL Server):
The following script sets up to use the myAccount@gmail.com account to email messages from SQL Server Express. You must replace myAccount@gmail.com and the @mailserver_name smpt.gmail.com with your email and smtp server information. Use SQL Management Studio> New Query and paste in the following:
--Enable Database Mail for Express--
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go
--Setup SMTP Outgoing Email Account--
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name ='myAccount',
@email_address = 'myAccount@gmail.com',
@display_name = 'From SQL Server',
@replyto_address = NULL,
@description = 'Mail Account for Database Mail',
@mailserver_name = 'smtp.gmail.com', -- the following fields are part of server definition
@mailserver_type = N'SMTP',
@port = 587,
@username = 'myAccount@gmail.com',
@password = 'myPwd',
@use_default_credentials = 0,
@enable_ssl = 'TRUE',
@account_id = NULL
--Create Mail Profile--
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'myEmailProfile',
@description = 'Profile needed for database mail'
--Add Profile Account--
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'myEmailProfile',
@account_name = 'myAccount',
@sequence_number = 1
--Add Principle Account--
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'myEmailProfile',
@principal_name = 'public',
@is_default = 1 ;
To verify setup, execute the following commands. Each query should return 1 record.
-- verify setup
select
* from msdb.[dbo].[sysmail_account]
select * from msdb.[dbo].[sysmail_profile]
select * from msdb.[dbo].[sysmail_profileaccount]
select * from msdb.[dbo].[sysmail_principalprofile]
Send a test message:
USE [msdb]
GO
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'useremail@xyz.com',
@subject = "Test email from SQL",
@body = "This is a Test"
GO
Add OPSDBA user into msdb so the OPSDBA user can execute the sp_send_dbmail.
In Management Studio:
- Go to Security>Logins. Right Click on OPSDBA and go to Properties.
- Click on User Mapping page.
- Check Map box for msdb.
Execute the following statement against msdb: Grant EXEC on sp_send_dbmail to opsdba
Now create the trigger to send email when out of range:
In SQL Management Studio, go to Triggers under Datatbl, right click and choose add trigger: