SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Intro to Database Mail in SQL 2005


Intro to Database Mail in SQL 2005

Author
Message
Ron West-292827
Ron West-292827
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 27

There was one thing missing in the implementation of 2005 database mail: Dynamic Profiles.

The company I work for sends lots of email on behalf of our reseller customers (impersonating them).

It took a ton of work to figure out how to do this and to supress some of the errors that were raised.

There was also some big security settings that needed to be figured out before I could implement this functionality.

since I can't attach the scripts directly, I'll add seperate posts for the steps involved.


Ron West-292827
Ron West-292827
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 27

Here's the security setup that's required:

--create the role in msdb
USE [msdb]
GO
/****** Object: DatabaseRole [CanSendEmail] Script Date: 11/03/2005 09:31:08 ******/
CREATE ROLE [CanSendEmail] AUTHORIZATION [dbo]

grant select on sysmail_account to CanSendEmail
grant select on sysmail_profile to CanSendEmail
grant select on sysmail_profileaccount to CanSendEmail
grant execute on sysmail_add_principalprofile_sp to CanSendEmail
grant execute on sysmail_add_profileaccount_sp to CanSendEmail
grant execute on sysmail_add_account_sp to CanSendEmail
grant execute on sysmail_add_profile_sp to CanSendEmail
grant execute on sp_send_dbmail to CanSendEmail


--create the login
CREATE LOGIN [emailonlyuser] WITH PASSWORD=N'somepassword', DEFAULT_DATABASE=[yourdatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

use yourdatabase
GO
exec sp_adduser 'emailonlyuser'
Grant execute on prcSendmail to emailonlyuser
GO
Use MSDB
GO
exec sp_adduser 'emailonlyuser','emailonlyuser','CanSendEmail'

GO


Ron West-292827
Ron West-292827
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 27

Here is the text of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



/* THIS IS A GENERAL SEND MAIL STORED PROCEDURE
Written by Ron West (ronw@ronwest99.com)
Dynamically creates accounts and profiles for Database
mail in SQL 2005

Make sure you run the permissions script before you create this proc.

*/


Create PROCEDURE [dbo].[prcSendMail]

@Address varchar(max),
@Subject varchar(255),
@Body varchar(max),
@From varchar(50) = 'ops@yourdomain.com',
@FromName varchar(100) = 'SQL Server',
@ReplyTo varchar(100) = 'ops@yourdomain.com',
@BCC varchar(max) = null,
@CC varchar(max) = null,
@Attachment varchar(250) = null,
@HTML bit = 0,
@Sensitivity varchar(12)='Normal',
@Importance varchar(6)='Normal',
@Debug bit=0
AS
BEGIN
SET NOCOUNT ON
Declare @body_format varchar(20),@mailitem_id int
declare @Success bit
declare @response varchar(255),@Name varchar(130)
declare @ProfileName sysname
Declare @AccountID int,@ProfileID int,@mailItemId int


if @Importance not in ('Low','Normal','High')
Set @Importance ='Normal'

if @Sensitivity not in ('Normal','Personal','Private','Confidential')
Set @Sensitivity='Normal'

if ltrim(rtrim(@FromName))='' or @FromName is null
set @FromName=@From

--this will be the key for the Profile AND Account
Set @ProfileName =@From + '/' + @FromName

if @HTML = 1
Set @body_format='HTML'
Else
Set @body_format='TEXT'


/**************************************************
Make sure the account exists and it's part of this profile
**************************************************/
SELECT @AccountID=Account_ID FROM msdb.dbo.sysmail_account WHERE [name]=@profilename
SELECT @ProfileID=Profile_ID FROM msdb.dbo.sysmail_profile WHERE [name]=@profilename

if @Debug=1
Begin
Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null')
Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null')
End

--create the profile if needed
if @ProfileID is null
Begin
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName,
@description = @ProfileName,
@profile_id =@ProfileID output

--give everybody access to use this profile
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@profile_id = @ProfileID,
@principal_name = 'public',
@is_default = 0 ;
End


--create the account (same name as profile) if needed
If @AccountID is null
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = @ProfileName,
@description = @From,
@email_address = @From,
@replyto_address = @ReplyTo,
@display_name = @FromName,
@mailserver_name = 'mail.yourdomain.com',
@Account_Id=@AccountID OUTPUT;

--create the relationship of account to profile
If not exists (Select * from msdb.dbo.sysmail_profileaccount where Profile_Id=@ProfileID and Account_Id=@AccountID)
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id =@ProfileID ,
@account_Id=@AccountID,
@sequence_number=1; --first in line

if @Debug=1
Begin
Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null')
Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null')
End

/**********************************************************
Now, send the mail using the specific account
**********************************************************/
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Address,
@subject = @Subject,
@body = @Body,
@Importance=@Importance,
@Sensitivity =@Sensitivity,
@copy_recipients=@CC,
@blind_copy_recipients=@BCC,
@body_format=@body_format,
@file_attachments=@Attachment,
@mailitem_id = @mailitem_id OUTPUT

Return(isnull(@mailitem_id, 0))
END



J-277808
J-277808
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 4
Really expected more from a professional. Although the content of the article was good, it was considerably deficient in providing any substantial content beyond 'point, click and turned on'. OK, well, what next?? . Had it not been for the reply posts, there really was not much to learn from.

THAT SAID: I do appreciate the fact that all of you, including the author, are willing to spend the time to create these posts. But please keep in mind, these posts don't benefit professionals, they benefit those who are learning.

Thank you,
twilson111
twilson111
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 95

I appreciated Ron West's followup posting of dynamically setting up email profiles/accounts via TSQL. I was recently questioned on sql05 abilities of producing emails with varible originating names which I know can be done manually. We have an CRM application and the challenge was to create an email from a triggered activity with the contactee's name in the "from" box. Rather than manage hundreds of profiles/accounts manually, the "on-the-fly" approach was the better scenario. Ron's code, with some mods, is just what I'm looking for!

Much Thanks!

Todd Wilson




Td Wilson
Neile Bermudes-317334
Neile Bermudes-317334
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 1

Can i just ask something to which i think i already know the answer: if i am managing my SQL 2000 servers in 2005 Management Studio, can i use Database Mail on these servers for SQL Alerting?

Thanks

Div


EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13669 Visits: 3894

Is there anyone out there who has replaced the SQL2005 xp_sendmail with a custom SP that calls sp_send_dbmail? As I only need send capability, I am thinking of doing this so we can loose MAPI-based mail on our SQL Server boxes.

eplacing xp-sendmail with a wrapper for sp_send_dbmail would allow us to de-couple the move to DB Mail from the application changes needed to move away from xp_sendmail.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Clinton Herring
Clinton Herring
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 12

If this was stated above anywhere sorry for the repeat...some Virus scanners block port 25 so you might need to add the follow exe to the Virus scanner port 25 exception list (if you don't disable that feature altogether; hopefully not) - DatabaseMail90.exe.

Also, if you have to set up a lot of servers here is some basic code, and I stress basic, that will add a public, default mail profile and account. The Profile name is picked up as the server name and the account name simply adds 'SQL ' to the front of that for an easy Outlook rule check...

Be sure to modify where stated before using.

/** Enables Database mail on SQL 2k5 & Creates a Mail Profile and Account *********/

/*
** assumes no profiles and accounts exist; New SQL 2k5 installtion
** assumes you are creating the first profile and account
** sets this profile as a public, default profile
** I use the <server name> as the profile name
** I use SQL + <server name> as the account name; make for easy Outlook rules
** be sure to add these executables, DatabaseMail90.exe,sqlservr.exe, to the Virus Scanner port 25 exception list
** be sure to add your corporate SMTP relay server name in the 2nd Exec command where needed
** be sure to replace <myplace> with your company domain name (yourcompany.com)
*/

use msdb
exec sp_configure 'Database Mail XPs', 1
reconfigure with override
waitfor delay '00:00:02'
declare @mailprofilename sysname
declare @mailprofileid int
declare @mailaccountname sysname
declare @mailaccountid int
declare @email_address sysname

set @mailprofilename = @@servername -- change as desired
set @mailaccountname = 'SQL ' + @mailprofilename -- change as desired
set @email_address = @mailprofilename + '@<myplace>

-- create a SQL mail profile
Exec dbo.sysmail_add_profile_sp
@mailprofilename
, 'Local default mail profile'
, @mailprofileid output
-- create a SQL mail account
Exec dbo.sysmail_add_account_sp
@mailaccountname
, @email_address
, @mailaccountname
, '' -- reply to address
, 'Local SQL smtp mail account'
, '<corporate SMTP relay server>' -- corporate SMTP relay server
, 'SMTP' -- mail type
, 25 -- port #
, null
, null
, 0 -- 0 anonymous auth, 1 NT auth
, 0 -- 1 enable ssl
, @mailaccountid output
-- associate the SQL mail profile and SQL mail account
Exec dbo.sysmail_add_profileaccount_sp
@mailprofileid
, @mailprofilename
, @mailaccountid
, @mailaccountname
, 1 -- sequence #
-- set the SQL mail profile as public and default
Exec dbo.sysmail_add_principalprofile_sp
2
, 'guest' -- public profile
, @mailprofileid
, @mailprofilename
, 1 -- default profile
go





alfred-169739
alfred-169739
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 309

This is an excellent article and instruction on setting up Database mail. Very impressed and I was able to setup and have my emailing working after following the article step by step guidelines.

Keep up the good works and continue to share your knowledge.

Alfred owusu(Canada)


Steve-438436
Steve-438436
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3

The article is good.

I set up the Database mail account and tested it and it worked fine.

I then set up a maintenance plan with a Notify Operator Task only and executed it to see if the message would be sent to me.

The task failed.

What am I missing?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search