Overview
Το database mail στο SQL Server είναι ένα από τα features που πρέπει κάποιος να χρησιμοποιεί καθώς έτσι μπορούμε να κάνουμε το SQL Server να μας μιλάει.
Αυτό που λείπει δυστυχώς είναι η δυνατότητα να μπορεί κάποιος να κάνει scripting τα υπάρχοντα profiles και accounts είτε γιατί θέλει να τα δει αλλά κυρίως γιατί θέλει να τα αντιγράψει σε κάποιον άλλον server.
Για αυτό το λόγο έχω δημιουργήσει το παρακάτω script που κάνει scripting όλα όσα χρειάζονται για την δημιουργία τους σε κάποιο άλλο SQL Server Instance
How it works
Ο παρακάτω script generator κάνει χρήση των παρακάτω system tables για να αντληθούν οι απαραίτητες πληροφορίες.
- msdb.dbo.sysmail_profile
- msdb.dbo.sysmail_profileaccount
- msdb.dbo.sysmail_account
- msdb.dbo.sysmail_server
- sys.credentials
Χρησιμοποιεί αυτές τις πληροφορίες στις stored procedures
- msdb.dbo.sysmail_add_profile_sp
- msdb.dbo.sysmail_add_account_sp
- msdb.dbo.sysmail_add_profileaccount_sp
για να δημιουργηθούν τα profiles και τα accounts.
Επίσης προσθέτει και τις απαραίτητες ρυθμίσεις για την ενεργοποίηση του database mail στον SQL Server.
NOTE
Να επισημανθεί ότι με το generator αυτόν γίνονται script όλα τα database mail profiles και account για αυτό και το script που δημιουργείται είναι μεγάλο και δεν βολεύει η απεικόνιση του σε grid ή text output. Για αυτό και το βγάζω σε xml ώστε να είναι εύκολο στην ανάγνωση του αλλά και στο copy & paste.
DBmail settings script generator
declare @crlf char(2) = char(13)+char(10);
declare @tab char(1) = char(9);
declare @dbmailscriptout varchar(max)='';
with dbmailset as (
select
p.name as profile_name
, case when p.description is null then 'null'
else p.description
end as profile_desc
, a.name as account_name
, case when a.description is null then 'null'
else a.description
end as account_desc
, a.email_address as account_email_address
, case when a.display_name is null then 'nulll'
else a.display_name
end as account_display_name
, case when a.replyto_address is null then 'null'
else a.replyto_address
end as account_replyto_address
, s.servername as account_servername
, s.servertype as account_servertype
, cast(s.port as varchar) as account_serverport
, cast(s.use_default_credentials as varchar(5)) as account_use_default_credentials
, cast(s.enable_ssl as varchar) as account_enable_ssl
, case when c.credential_identity is null then 'null'
else c.credential_identity
end as account_credential_identity
, case when c.credential_identity is null then 'null'
else '*** set account password ***'
end as account_credential_identity_password
, cast(pa.sequence_number as varchar) as profile_account_sequence_number
from msdb.dbo.sysmail_profile as p
inner join msdb.dbo.sysmail_profileaccount as pa on p.profile_id = pa.profile_id
inner join msdb.dbo.sysmail_account as a on pa.account_id = a.account_id
left outer join msdb.dbo.sysmail_server as s on a.account_id = s.account_id
left outer join sys.credentials as c on s.credential_id = c.credential_id
)
select
@dbmailscriptout+=@crlf+@crlf +'--'+REPLICATE('=',100)+@crlf
+ '-- Profile : ' + profile_name + @crlf
+ '--'+REPLICATE('=',100)+@crlf
+ @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ '-- Create Profile : ' + profile_name + @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ 'if not exists(select * from msdb.dbo.sysmail_profile where name = ' + quotename(profile_name,'''') + ')' + @crlf
+ 'begin' + @crlf
+ @tab + 'execute msdb.dbo.sysmail_add_profile_sp' + @crlf
+ replicate(@tab,3) + ' @profile_name = ' + quotename(profile_name,'''') + @crlf
+ replicate(@tab,3) + ',@description = ' + quotename(profile_desc,'''') + ';' + @crlf
+ 'end' + @crlf
+ 'go' + @crlf
+ @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ '-- Create Account : ' + account_name + @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ 'if not exists(select * from msdb.dbo.sysmail_account where name = ' + quotename(account_name,'''') + ')' + @crlf
+ 'begin' + @crlf
+ @tab + 'execute msdb.dbo.sysmail_add_account_sp ' + @crlf
+ replicate(@tab,3) + ' @account_name = ' + quotename(account_name,'''') + @crlf
+ replicate(@tab,3) + ',@email_address = ' + quotename(account_email_address,'''') + @crlf
+ replicate(@tab,3) + ',@display_name = ' + quotename(account_display_name,'''') + @crlf
+ replicate(@tab,3) + ',@replyto_address = ' + quotename(account_replyto_address,'''') + @crlf
+ replicate(@tab,3) + ',@description = ' + quotename(account_desc,'''') + @crlf
+ replicate(@tab,3) + ',@mailserver_name = ' + quotename(account_servername,'''') + @crlf
+ replicate(@tab,3) + ',@mailserver_type = ' + quotename(account_servertype,'''') + @crlf
+ replicate(@tab,3) + ',@port = ' + account_serverport + @crlf
+ replicate(@tab,3) + ',@username = ' + quotename(account_credential_identity,'''') + @crlf
+ replicate(@tab,3) + ',@password = ' + quotename(account_credential_identity_password,'''') + @crlf
+ replicate(@tab,3) + ',@use_default_credentials = ' + account_use_default_credentials + @crlf
+ replicate(@tab,3) + ',@enable_ssl = ' + account_enable_ssl + ';' + @crlf
+ 'end ' + @crlf
+ 'go' + @crlf
+ @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ '-- Link Account : ' + account_name + ' with Profile : ' + profile_name + @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ 'if not exists(select * from msdb.dbo.sysmail_profileaccount as pa' + @crlf
+ replicate(@tab,4) + 'inner join msdb.dbo.sysmail_profile as p on pa.profile_id = p.profile_id' + @crlf
+ replicate(@tab,4) + 'inner join msdb.dbo.sysmail_account as a on pa.account_id = a.account_id' + @crlf
+ replicate(@tab,4) + 'where p.name = '+ quotename(profile_name,'''') + ' and a.name = ' + quotename(account_name,'''') +')' + @crlf
+ 'begin' + @crlf
+ @tab + 'execute msdb.dbo.sysmail_add_profileaccount_sp' + @crlf
+ replicate(@tab,3) + ' @profile_name = ' + quotename(profile_name,'''') + @crlf
+ replicate(@tab,3) + ',@account_name = ' + quotename(account_name,'''') + @crlf
+ replicate(@tab,3) + ',@sequence_number = ' + profile_account_sequence_number + ';' + @crlf
+ 'end' + @crlf
+ 'go' + @crlf
from dbmailset
-- add database mail activation in script
set @dbmailscriptout =
'--'+REPLICATE('-',100)+@crlf
+ '-- database mail activation ' + @crlf
+ '--'+REPLICATE('-',100)+@crlf
+ 'use master ' + @crlf
+ 'go' + @crlf
+ 'sp_configure ''show advanced options'',1 ;'+ @crlf
+ 'go' + @crlf
+ 'reconfigure with override ' + @crlf
+ 'go' + @crlf
+ 'sp_configure ''database mail xps'',1 ;' + @crlf
+ 'go' + @crlf
+ 'reconfigure' + + @crlf
+ 'go' + @crlf
+ @dbmailscriptout
-- script final output
select @dbmailscriptout FOR XML PATH('')
//Antonios Chatzipavlis