Sorry. Sometimes I miss the point. DBMail setup.

Here's the code I use for setting up DBMail - I carved the other junk out. There are probably several ways to do this and my way is just one opinion. Others may have a better solution and I'd be excited to see their approach. I simply needed it done, couldn't find a decent example to start with so I started slingin' code for better or worse.

You may want to test it a few times on dev servers - don't trust me. <lol>

(that wasn't an <evil lol> BTW)

Also in the "you may need this" department: The SSMS tasks to [remove] DBMail are (from the wizard of course):

Configure DBMail

Manage Database Mail accounts and profiles

View, change, or delete an existing profile

[Delete] button

[Next] button

[Finish] button

Configure DBMail

Manage Database Mail accounts and profiles

View, change, or delete an existing account

[Delete] button

[Next] button

[Finish] button

Don't sweat the 'Default DBA' thing. Everybody here was a noob at one point in their career. The trick is to never panic. SQLServerCentral is here for you and Google is your friend. Except when it comes to T-SQL script examples for setting up DBMail, apparently. <grin>

I sure hope this comes across as 'Courier New' or it's gonna look ugly...

[font="Courier New"]

/*

** Script name.....: DBMail_Setup.sql

** Blame...........: Ken

** Date written....: 12/04/2012

** Purpose.........: Automate

** o Enabling Database Mail

** o Creating an email account for DBMail

** o Creating an email profile for DBMail

** o Associating an email account with a profile

** o Making the new profile the default profile

** o Create an operator based on the new profile

** o Sending a test email message

*/

/*

** "Challenger, go at throttle up..."

*/

SET NOCOUNT ON

-- Declare variables

DECLARE @accountname NVARCHAR(128), -- DBMail EMail account

@profilename NVARCHAR(128), -- DBMail EMail profile

@displayname NVARCHAR(128), -- DBMail EMail display name

@emailaddress NVARCHAR(128), -- DBMail EMail address used

@mailservername NVARCHAR(128), -- DBMail EMail server

@operatorname NVARCHAR(128), -- SQLAgent operator name

@testemailto NVARCHAR(128), -- DBMail send test email to address

@errorflag TINYINT -- Flag for script errors

-- Initialize variables

/*

*****************************************

** **

** MODIFY THESE VARIABLES IF NECESSARY **

** **

*****************************************

*/

SELECT @accountname = '<OperatorAlias, sysname, DBA>',

@profilename = '<OperatorAlias, sysname, DBA>',

@operatorname = '<OperatorAlias, sysname, DBA>',

@emailaddress = '<OperatorAlias, sysname, DBA>@coopertire.com',

@mailservername = '<SMTPServer, sysname, SMTP.nowhere.com>',

@operatorname = '<OperatorAlias, sysname, DBA>',

@testemailto = '<TestEMailTo, sysname, ken@nowhere.com>'

/*

** Script variables

*/

SELECT @errorflag = 0

-- Output script settings line 1

SELECT @accountname AS accountname,

@profilename AS profilename,

@emailaddress AS emailaddress,

@mailservername AS mailservername,

@operatorname AS operatorname,

@testemailto AS testemailto

-- Enable DBMail

IF (SELECT [value_in_use]

FROM sys.configurations

WHERE [name] = 'Database Mail XPs') = 1

BEGIN

PRINT 'CHECK: Database Mail already enabled.'

END

ELSE

BEGIN

PRINT 'Enabling DBMail...'

USE [msdb];

EXEC sp_configure 'show advanced', 1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Database Mail XPs', 1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced', 0;

RECONFIGURE WITH OVERRIDE;

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Create a Database Mail account

IF EXISTS (

SELECT *

FROM msdb.dbo.sysmail_account a

WHERE a.name = @AccountName)

BEGIN

PRINT 'CHECK: DBMail account ' + @accountname + ' already exists.'

END

ELSE

BEGIN

PRINT 'Creating Account ' + @accountname + '...'

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @accountname,

@description = 'Mail account for Findlay Database Administration team.',

@email_address = @emailaddress,

@display_name = @displayname,

@mailserver_name = @mailservername;

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Create a Database Mail profile

IF EXISTS (

SELECT *

FROM msdb.dbo.sysmail_profile p

WHERE p.name = @ProfileName)

BEGIN

PRINT 'CHECK: DBMail profile ' + @profilename + ' already exists.'

END

ELSE

BEGIN

PRINT 'Creating Profile ' + @profilename + '...'

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @profilename,

@description = 'Profile used for Findlay Database Administration team.' ;

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Add the account to the profile

IF EXISTS(

SELECT *

FROM msdb.dbo.sysmail_profileaccount pa

JOIN msdb.dbo.sysmail_profile p

ON pa.profile_id = p.profile_id

JOIN msdb.dbo.sysmail_account a

ON pa.account_id = a.account_id

WHERE p.name = @ProfileName

AND a.name = @AccountName)

BEGIN

PRINT 'CHECK: DBMail profile/account ' + @profilename + '/' + @accountname + ' already exists.'

END

ELSE

BEGIN

PRINT 'Creating Profile/Account ' + @profilename + '/' + @accountname + '...'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @profilename,

@account_name = @accountname,

@sequence_number =1 ;

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Grant access to the profile to all users in the msdb database

IF (SELECT prinprof.is_default

FROM sys.database_principals dbprin,

msdb.dbo.sysmail_principalprofile prinprof,

msdb.dbo.sysmail_profile prof

WHERE dbprin.principal_id = msdb.dbo.get_principal_id(prinprof.principal_sid)

AND prof.profile_id = prinprof.profile_id

AND prof.name = @profilename

) = 1

BEGIN

PRINT 'CHECK: DBMail profile ' + @profilename + ' is already the default email profile.'

END

ELSE

BEGIN

PRINT 'Making ' + @profilename + ' the default email profile...'

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @profilename,

@principal_name = 'public',

@is_default = 1 ;

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Add an operator

IF EXISTS (SELECT [name] FROM msdb.dbo.sysoperators WHERE [name] LIKE @operatorname)

BEGIN

PRINT 'CHECK: Operator ' + @operatorname + ' already exists.'

END

ELSE

BEGIN

PRINT 'Creating ' + @operatorname + ' operator...'

EXEC msdb.dbo.sp_add_operator

@name = @operatorname,

@enabled = 1,

@weekday_pager_start_time = 90000,

@weekday_pager_end_time = 180000,

@saturday_pager_start_time = 90000,

@saturday_pager_end_time = 180000,

@sunday_pager_start_time = 90000,

@sunday_pager_end_time = 180000,

@pager_days = 0,

@email_address = @emailaddress,

@category_name = N'[Uncategorized]'

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

END

-- Send test message

PRINT 'Sending test email message...'

EXEC msdb.dbo.sp_send_dbmail @profile_name = @profilename,

@recipients = '<TestEMailTo, sysname, ken@nowhere.com>',

@subject = 'Test alert message',

@body = 'Please ignore this message - it is only a test.'

IF @@ERROR != 0 SELECT @errorflag = @errorflag + 1

-- End of script

IF @errorflag != 0

PRINT 'Script completed with ' + CONVERT(VARCHAR(5), @errorflag) + ' warning(s).'

ELSE

PRINT 'Script completed successfully.'

SET NOCOUNT OFF

GO

[/font]