JosephStyons February 2016

How can I set up DBMail to use a gmail account through a SQL Script

I want to send email through my gmail account using SQL Server's dbmail.

I'd like to do this setup purely from a SQL Script. What's the best way to do that?

I'm using SQL Server 2014.

Answers


JosephStyons February 2016

Here is the answer I found for myself.

USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO
use [msdb];

/*
before you run this, replace all occurrences of
   DOMAIN\USERNAME
with your actual domain and user name
--*/
declare @profilename nvarchar(max) = 'My profile';
declare @accountname nvarchar(max) = 'My gmail account';
declare @mygmailaccount nvarchar(100) = 'someone@gmail.com';
declare @mygmailpassword nvarchar(100) = 'YourPassword';  --plain text

--create the profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
  @profile_name = @profilename
 ,@description = 'My test mail profile';

--create the account
EXECUTE msdb.dbo.sysmail_add_account_sp
  @account_name    = 'Gmail'
 ,@description     = 'My gmail account, for testing'
 ,@email_address   = @mygmailaccount
 ,@replyto_address = @mygmailaccount
 ,@display_name    = 'Test email'
 ,@mailserver_name = 'smtp.gmail.com'
 ,@mailserver_type = 'SMTP'  --case sensitive!
 ,@port            = 587
 ,@username        = @mygmailaccount
 ,@password        = @mygmailpassword
 ,@enable_ssl      = 1;

--tie the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = @profilename
 ,@account_name = @accountname
 ,@sequence_number = 1;

--give yourself permission to use what you created
EXEC sp_addrolemember N'DatabaseMailUserRole', N'DOMAIN\USERNAME'
ALTER AUTHORIZATION ON SCHEMA::[DOMAIN\USERNAME] TO [DatabaseMailUserRole]

--send a test message
declare @msg nvarchar(max) = 'Receipt of this message confirms that SQL DBMail is working correctly on server ' + @@servername + ' as of ' + convert(nvarchar(max),getdate()) + '.';
exec msdb.dbo.sp_send_dbmail @profilename, @mygmailaccount, null, null, @msg, 'TEXT', 'Normal';

Post Status

Asked in February 2016
Viewed 2,692 times
Voted 9
Answered 1 times

Search




Leave an answer