LLI Wiki — My Digital Knowledge Base

SQL Server : générer le script de configuration Database Mail

Script T-SQL qui extrait profils, comptes et serveurs SMTP de msdb pour générer un script de recréation de Database Mail.

Ce script T-SQL lit la configuration Database Mail existante (profils, comptes, serveurs SMTP) dans msdb et génère un script de recréation à exécuter sur une autre instance. Le mot de passe n'est jamais lu : une valeur factice est écrite dans le script généré.

Principe

Script générateur

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--------------------------------------------------------------------------------------------------
-- BEGIN Mail Settings ' + p.name + '
--------------------------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS 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 + '''<PASSWORD>''' 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
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''')
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END
--IF EXISTS associate accounts to profiles
---------------------------------------------------------------------------------------------------
-- Drop Settings For ' + p.name + '
--------------------------------------------------------------------------------------------------
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END
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
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  '
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.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

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
                    SELECT 1 UNION ALL SELECT 1 UNION ALL
                    SELECT 1 UNION ALL SELECT 1 UNION ALL
                    SELECT 1 UNION ALL SELECT 1 UNION ALL
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
       ItemSplit(
                 ItemOrder,
                 Item
                ) as (
                      SELECT N,
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
                      FROM Tally
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
                     )
  select
    row_number() over (order by ItemOrder) as ItemID,
    Item
  from ItemSplit

Exemple de script généré

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

-- BEGIN Mail Settings sqlmail
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = 'sqlmail')
  BEGIN
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = 'sqlmail',
      @description  = 'sqlmail.';
  END

  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = 'sqlmail')
  BEGIN
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = 'sqlmail',
    @email_address           = 'user@example.com',
    @display_name            = 'SQLMail',
    @replyto_address         = 'user@example.com',
    @description             = 'sqlmail.',
    @mailserver_name         = 'smtp.example.com',
    @mailserver_type         = 'SMTP',
    @port                    = '25',
    @username                =  NULL ,
    @password                =  NULL ,
    @use_default_credentials =  0 ,
    @enable_ssl              =  0 ;
  END

IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
              WHERE p.name = 'sqlmail'
                AND a.name = 'sqlmail')
  BEGIN
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = 'sqlmail',
      @account_name = 'sqlmail',
      @sequence_number = 1 ;
  END

Remarques

Mastodon LLIMastodon SocialFosstodon