建立 Database Mail 設定檔Create a Database Mail Profile

本主題的適用對象: 是SQL Server沒有Azure SQL Database沒有Azure SQL 資料倉儲沒有Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

您可以使用 [Database Mail 組態精靈] 或 Transact-SQLTransact-SQL,建立 Database Mail 公用和私人設定檔。Use either the Database Mail Configuration Wizard or Transact-SQLTransact-SQL to create Database Mail public and private profiles. 如需郵件設定檔的詳細資訊,請參閱 Database Mail 設定檔For more information about mail profiles see Database Mail Profile.

開始之前Before You Begin

必要條件Prerequisites

替該設定檔建立一個或多個 Database Mail 帳戶。Create one or more Database Mail accounts for the profile. 如需建立 Database Mail 帳戶的詳細資訊,請參閱 建立 Database Mail 帳戶For more information about creating Database Mail accounts, see Create a Database Mail Account.

安全性Security

公用設定檔可以讓任何可存取 msdb 資料庫的使用者使用該設定檔來傳送電子郵件。A public profile allows any user with access to the msdb database to send e-mail using that profile. 使用者或角色可以使用私人設定檔。A private profile can be used by a user or by a role. 為角色授與設定檔的存取權限時,會建立能夠更輕鬆維護的架構。Granting roles access to profiles creates a more easily maintained architecture. 您必須是 msdb 資料庫中之 DatabaseMailUserRole 的成員,而且至少可以存取一個 Database Mail 設定檔,才能傳送郵件。To send mail you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.

權限Permissions

建立設定檔帳戶以及執行預存程序的使用者,應該是系統管理員 (sysadmin) 固定伺服器角色的成員。The user creating the profiles accounts and executing stored procedures should be a member of the sysadmin fixed server role.

使用 Database Mail 組態精靈Using Database Mail Configuration Wizard

建立 Database Mail 設定檔To Create a Database Mail profile

  • 在 [物件總管] 中,連接到想要在其上設定 Database Mail 的 SQL ServerSQL Server 執行個體,並展開伺服器樹狀目錄。In Object Explorer, connect to the SQL ServerSQL Server instance you want to configure Database Mail on, and expand the server tree.

  • 展開 [管理] 節點。Expand the Management node

  • 按兩下 Database Mail,開啟 [Database Mail 組態精靈]。Double click Database Mail to open the Database Mail Configuration Wizard.

  • 在 [選取組態工作] 頁面上,選取 [管理 Database Mail 帳戶和設定檔] 選項,然後按一下 [下一步]。On the Select Configuration Task page, select Manage Database Mail accounts and profiles option and click Next.

  • 在 [管理設定檔和帳戶] 頁面上,選取 [建立新設定檔] 選項,然後按一下 [下一步]。On the Manage Profiles and Accounts page, select Create a new profile option, and click Next.

  • 在 [新增設定檔] 頁面上,指定設定檔名稱、描述並加入要併入設定檔中的帳戶,然後按一下 [下一步]。On the New Profile page, specifiy the Profile name, Description and add accounts to be included in the profile, and click Next.

  • 在 [完成精靈] 頁面上,檢閱要執行的動作,然後按一下 [完成] 完成新設定檔的建立。On the Complete the Wizard page, review the actions to be performed and click Finish to complete creating the new profile.

  • 若要設定 Database Mail 私人設定檔:To configure a Database Mail private profile:

    • 開啟 [Database Mail 組態精靈]。Open the Database Mail Configuration Wizard.

    • 在 [選取組態工作] 頁面上,選取 [管理 Database Mail 帳戶和設定檔] 選項,然後按一下 [下一步]。On the Select Configuration Task page, select Manage Database Mail accounts and profiles option, and click Next.

    • 在 [管理設定檔和帳戶] 頁面上,選取 [管理設定檔安全性] 選項,然後按一下 [下一步]。On the Manage Profiles and Accounts page, select Manage profile security option and click Next.

    • 在 [私人設定檔] 索引標籤中,選取想要設定之設定檔的核取方塊,然後按一下 [下一步]。In the Private Profiles tab, select the check box for the profile you would like to configure and click Next.

    • 在 [完成精靈] 頁面上,檢閱要執行的動作,然後按一下 [完成] 完成設定檔的設定。On the Complete the Wizard page, review the actions to be performed and click Finish to complete configuring the profile.

  • 若要設定 Database Mail 公用設定檔:To configure a Database Mail public profile:

    • 開啟 [Database Mail 組態精靈]。Open the Database Mail Configuration Wizard.

    • 在 [選取組態工作] 頁面上,選取 [管理 Database Mail 帳戶和設定檔] 選項,然後按一下 [下一步]。On the Select Configuration Task page, select Manage Database Mail accounts and profiles option, and click Next.

    • 在 [管理設定檔和帳戶] 頁面上,選取 [管理設定檔安全性] 選項,然後按一下 [下一步]。On the Manage Profiles and Accounts page, select Manage profile security option and click Next.

    • 在 [公用設定檔] 索引標籤中,選取想要設定之設定檔的核取方塊,然後按一下 [下一步]。In the Public Profiles tab, select the check box for the profile you would like to configure and click Next.

    • 在 [完成精靈] 頁面上,檢閱要執行的動作,然後按一下 [完成] 完成設定檔的設定。On the Complete the Wizard page, review the actions to be performed and click Finish to complete configuring the profile.

使用 Transact-SQLUsing Transact-SQL

建立 Database Mail 私人設定檔To Create a Database Mail private profile

  • 連接到 SQL ServerSQL Server 執行個體。Connect to the SQL ServerSQL Server instance.

  • 若要建立新的設定檔,請執行系統預存程序 sysmail_add_profile_sp (Transact-SQL),如下所示:To create a new profile, run the system stored procedure sysmail_add_profile_sp (Transact-SQL) as follows:

    EXECUTEmsdb.dbo.sysmail_add_profile_spEXECUTEmsdb.dbo.sysmail_add_profile_sp

    @profile_name = '設定檔名稱'@profile_name = 'Profile Name'

    @description = '描述'@description = 'Desciption'

    其中, @profile_name 是設定檔的名稱,而 @description 是設定檔的描述。where @profile_name is the name of the profile, and @description is the description of the profile. 這個參數是選擇性的。This parameter is optional.

  • 針對每個帳戶,執行預存程序 sysmail_add_profileaccount_sp (Transact-SQL),如下所示:For each account, run the stored procedure sysmail_add_profileaccount_sp (Transact-SQL) as follows:

    EXECUTEmsdb.dbo.sysmail_add_profileaccount_spEXECUTEmsdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = '設定檔的名稱'@profile_name = 'Name of the profile'

    @account_name = '帳戶的名稱'@account_name = 'Name of the account'

    @sequence_number = '帳戶在設定檔內的序號@sequence_number = 'sequence number of the account within the profile. ''

    其中, @profile_name 是設定檔的名稱,而 @account_name 是要加入設定檔的帳戶名稱,而 @sequence_number 可決定帳戶在設定檔中的使用順序。where @profile_name is the name of the profile, and @account_name is the name of the account to add to the profile, @sequence_number determines the order in which the accounts are used in the profile.

  • 針對使用此設定檔傳送郵件的每個資料庫角色或使用者,授與設定檔的存取權。For each database role or user that will send mail using this profile, grant access to the profile. 作法是執行預存程序 sysmail_add_principalprofile_sp (Transact-SQL),如下所示:To do this, run the stored procedure sysmail_add_principalprofile_sp (Transact-SQL) as follows:

    EXECUTEmsdb.sysmail_add_principalprofile_spEXECUTEmsdb.sysmail_add_principalprofile_sp

    @profile_name = '設定檔的名稱'@profile_name = 'Name of the profile'

    @ principal_name = '資料庫使用者或角色的名稱'@ principal_name = 'Name of the database user or role'

    @is_default = '預設設定檔狀態 '@is_default = 'Default Profile status '

    其中, @profile_name 是設定檔的名稱,而 @principal_name 是資料庫使用者或角色的名稱,而 @is_default 可決定此設定檔是否為資料庫使用者或角色的預設值。where @profile_name is the name of the profile, and @principal_name is the name of the database user or role, @is_default determines the whether this profile is the default for the database user or role.

    下列範例會建立 Database Mail 帳戶、建立 Database Mail 私人設定檔,然後將帳戶加入設定檔,並將設定檔的存取權授與 msdb 資料庫中的 DBMailUsers 資料庫角色。The following example creates a Database Mail account, creates a Database Mail private profile, then adds the account to the profile and grants access to the profile to the DBMailUsers database role in the msdb database.

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'AdventureWorks Administrator',  
    @description = 'Mail account for administrative e-mail.',  
    @email_address = 'dba@Adventure-Works.com',  
    @replyto_address = 'danw@Adventure-Works.com',  
    @display_name = 'AdventureWorks Automated Mailer',  
    @mailserver_name = 'smtp.Adventure-Works.com' ;  

-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'AdventureWorks Administrator Profile',  
    @description = 'Profile used for administrative mail.' ;  

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'AdventureWorks Administrator Profile',  
    @account_name = 'AdventureWorks Administrator',  
    @sequence_number =1 ;  

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'AdventureWorks Administrator Profile',  
    @principal_name = 'ApplicationUser',  
    @is_default = 1 ;  

建立 Database Mail 公用設定檔To Create a Database Mail public profile

  • 連接到 SQL ServerSQL Server 執行個體。Connect to the SQL ServerSQL Server instance.

  • 若要建立新的設定檔,請執行系統預存程序 sysmail_add_profile_sp (Transact-SQL),如下所示:To create a new profile, run the system stored procedure sysmail_add_profile_sp (Transact-SQL) as follows:

    EXECUTEmsdb.dbo.sysmail_add_profile_spEXECUTEmsdb.dbo.sysmail_add_profile_sp

    @profile_name = '設定檔名稱'@profile_name = 'Profile Name'

    @description = '描述'@description = 'Desciption'

    其中, @profile_name 是設定檔的名稱,而 @description 是設定檔的描述。where @profile_name is the name of the profile, and @description is the description of the profile. 這個參數是選擇性的。This parameter is optional.

  • 針對每個帳戶,執行預存程序 sysmail_add_profileaccount_sp (Transact-SQL),如下所示:For each account, run the stored procedure sysmail_add_profileaccount_sp (Transact-SQL) as follows:

    EXECUTEmsdb.dbo.sysmail_add_profileaccount_spEXECUTEmsdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = '設定檔的名稱'@profile_name = 'Name of the profile'

    @account_name = '帳戶的名稱'@account_name = 'Name of the account'

    @sequence_number = '帳戶在設定檔內的序號@sequence_number = 'sequence number of the account within the profile. ''

    其中, @profile_name 是設定檔的名稱,而 @account_name 是要加入設定檔的帳戶名稱,而 @sequence_number 可決定帳戶在設定檔中的使用順序。where @profile_name is the name of the profile, and @account_name is the name of the account to add to the profile, @sequence_number determines the order in which the accounts are used in the profile.

  • 若要授與公用存取權,請執行預存程序 sysmail_add_principalprofile_sp (Transact-SQL),如下所示:To grant public access, run the stored procedure sysmail_add_principalprofile_sp (Transact-SQL) as follows:

    EXECUTEmsdb.sysmail_add_principalprofile_spEXECUTEmsdb.sysmail_add_principalprofile_sp

    @profile_name = '設定檔的名稱'@profile_name = 'Name of the profile'

    @ principal_name = 'public0'@ principal_name = 'public or 0'

    @is_default = '預設設定檔狀態 '@is_default = 'Default Profile status '

    其中, @profile_name 是設定檔的名稱,而 @principal_name 指出這是公用設定檔,而 @is_default 可決定此設定檔是否為資料庫使用者或角色的預設值。where @profile_name is the name of the profile, and @principal_name to indicate this this is a public profile, @is_default determines the whether this profile is the default for the database user or role.

    下列範例會建立 Database Mail 帳戶、建立 Database Mail 私人設定檔,然後將帳戶加入設定檔,並授與設定檔的公用存取權。The following example creates a Database Mail account, creates a Database Mail private profile, then adds the account to the profile and grants public access to the profile.

-- Create a Database Mail account  

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'AdventureWorks Public Account',  
    @description = 'Mail account for use by all database users.',  
    @email_address = 'db_users@Adventure-Works.com',  
    @replyto_address = 'danw@Adventure-Works.com',  
    @display_name = 'AdventureWorks Automated Mailer',  
    @mailserver_name = 'smtp.Adventure-Works.com' ;  

-- Create a Database Mail profile  

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'AdventureWorks Public Profile',  
    @description = 'Profile used for administrative mail.' ;  

-- Add the account to the profile  

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'AdventureWorks Public Profile',  
    @account_name = 'AdventureWorks Public Account',  
    @sequence_number =1 ;  

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

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'AdventureWorks Public Profile',  
    @principal_name = 'public',  
    @is_default = 1 ;