June 11, 2012 at 8:17 am
Thank you Lowell. This saved a lot of time!
August 17, 2012 at 9:59 pm
I've got to echo the previous comments. Thanks alot! I only wished I had found it sooner. Added to the briefcase.
Luke C
MCSE: Data Platform, MCP, MCTS, MCITP - Database Administrator & Database Developer
September 11, 2012 at 3:27 am
+1 Lowell!
It makes my life so much easier this script. Great stuff.
December 19, 2012 at 8:49 am
Lowell,
I just found your script and will be adding it to my collection. Thank you for the work.
I did make one change. I removed the cte at the end and just did a PRINT @TheResults.
This way the created script is in the Messages tab and I can just copy and paste in a new query to run on my migrated server.
Thanks
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
March 1, 2013 at 7:44 am
Wow, this just saved me a ton of time. In the process of moving data centers and have several accounts/profiles to move over.
Thanks Lowell!!
April 25, 2013 at 2:34 pm
Very helpful, script allows me to uniformly add SMTP mail to various SQL servers from one original source - thank you
June 24, 2013 at 5:25 am
I love Powershell
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();
https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]
June 24, 2013 at 6:49 am
mrrobsewell (6/24/2013)
I love Powershell[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();
That's interesting; i bet with some enhancements, that could do the same thing my script was;
i see quite a few differences, so with a 1:1 comparison, my little script is still better, but i'd have to wrestle with powershell and see if I can coax all the same settings out that my script does;
i bet the If Not Exists() is just a setting in SMO.
The scripting of an account in powershell needs a lot of work;
EXEC msdb.dbo.sysmail_add_account_sp
@account_name=N'sqlnotifications@gmail.com',
@email_address=N'sqlnotifications@gmail.com',
@display_name=N'SQL Notifications',
@replyto_address=N'sqlnotifications@gmail.com',
@description=N'Google gMail account'
but my script gets quite a few more settings:
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'sqlnotifications@gmail.com')
BEGIN
--CREATE Account [sqlnotifications@gmail.com]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'sqlnotifications@gmail.com',
@email_address = 'sqlnotifications@gmail.com',
@display_name = 'SQL Notifications',
@replyto_address = 'sqlnotifications@gmail.com',
@description = 'Google gMail account',
@mailserver_name = 'smtp.googlemail.com',
@mailserver_type = 'SMTP',
@port = '587',
@username = 'sqlnotifications@gmail.com',
@password = 'NotTheRealPassword',
@use_default_credentials = 0 ,
@enable_ssl = 0 ;
END --IF EXISTS account
Lowell
February 20, 2014 at 10:28 pm
This is brilliant, thanks Lowell.
April 4, 2014 at 5:45 am
Very nice, Lowell!! Thank you!
May 30, 2014 at 7:05 am
Thank you for this extremely useful script. I was just setting up our new server and had disabled some notices in procedures pending learning (again) how to set up email and which accounts we had used when we set up the other server three years ago.
I found this thread and your script. Copied it. Ran it. Ran the result. Done.
That's a nice thing you did. Many thanks.
May 30, 2014 at 1:24 pm
Thanks for the feedback Charles! It still gives me that warm feeling of a job well done when someone says i helped them.
I think this one has been a script a lot of folks have added to their toolbelt;
So glad it worked out well for you; I guess the only thing you had to do was find and change the password for your SMTP settings?
Lowell
September 2, 2014 at 9:18 pm
A good script that I have used a few times. Just found out today that because it is based on profiles (inner join ...) it does not script accounts that don't have a profile. I added the following ...
-- Accounts without profiles (added 4-Sep-2014)
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + a.name + ' (no profile)
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
--CREATE Account [' + a.name + ']
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
@email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
@display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
@replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
@description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
@mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
@mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
@port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
@username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
@password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
--#################################################################################################
-- Drop Settings For ' + a.name + '
--#################################################################################################
/*
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
END
*/
'
FROM msdb.dbo.sysmail_account a
LEFT JOIN msdb.dbo.sysmail_profileaccount pa ON a.account_id = pa.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
WHERE pa.profile_id IS NULL
November 1, 2014 at 12:49 pm
This is an awesome script. I am adding it to my box of scripts and putting a reference to this thread int he comment block so that I can't forget where it came from.
Dave
February 5, 2015 at 11:43 am
Lowell,
Excellent script. You have saved me few hours of work. Thanks!
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply