Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Intro to Database Mail in SQL 2005 Expand / Collapse
Author
Message
Posted Monday, February 13, 2006 2:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:08 PM
Points: 3, 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.

 

 

 

 

Post #258041
Posted Monday, February 13, 2006 2:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:08 PM
Points: 3, 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

 

Post #258043
Posted Monday, February 13, 2006 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:08 PM
Points: 3, 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 


 

 

Post #258044
Posted Thursday, April 13, 2006 9:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 29, 2008 11:11 AM
Points: 1, 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,
Post #273277
Posted Thursday, May 11, 2006 1:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 26, 2008 10:45 AM
Points: 200, 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
Post #279417
Posted Wednesday, June 7, 2006 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 3:41 AM
Points: 1, 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

Post #285520
Posted Monday, January 1, 2007 2:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:46 AM
Points: 2,879, Visits: 3,229

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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #333623
Posted Friday, January 5, 2007 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 1:35 PM
Points: 60, 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




Post #334690
Posted Friday, January 26, 2007 11:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:05 PM
Points: 3, Visits: 308

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)

Post #340168
Posted Monday, August 13, 2007 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2008 9:29 AM
Points: 2, 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?

 

Post #390272
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse