sysmail_add_account_sp (Transact-SQL)sysmail_add_account_sp (Transact-SQL)

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

建立新的 Database Mail 帳戶來保留 SMTP 帳戶的相關資訊。Creates a new Database Mail account holding information about an SMTP account.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


sysmail_add_account_sp  [ @account_name = ] 'account_name',  
    [ @email_address = ] 'email_address' ,  
    [ [ @display_name = ] 'display_name' , ]  
    [ [ @replyto_address = ] 'replyto_address' , ]  
    [ [ @description = ] 'description' , ]  
    [ @mailserver_name = ] 'server_name'   
    [ , [ @mailserver_type = ] 'server_type' ]  
    [ , [ @port = ] port_number ]  
    [ , [ @username = ] 'username' ]  
    [ , [ @password = ] 'password' ]  
    [ , [ @use_default_credentials = ] use_default_credentials ]  
    [ , [ @enable_ssl = ] enable_ssl ]  
    [ , [ @account_id = ] account_id OUTPUT ]  

引數Arguments

[ @account_name = ] 'account_name'[ @account_name = ] 'account_name'
這是要加入的帳戶名稱。The name of the account to add. account_namesysname,沒有預設值。account_name is sysname, with no default.

[ @email_address = ] 'email_address'[ @email_address = ] 'email_address'
傳送訊息的來源電子郵件地址。The e-mail address to send the message from. 這個地址必須是網際網路電子郵件地址。This address must be an internet e-mail address. email_addressnvarchar (128),沒有預設值。email_address is nvarchar(128), with no default. 例如,帳戶 SQL ServerSQL Server代理程式可能傳送電子郵件地址從SqlAgent@Adventure-Works.comFor example, an account for SQL ServerSQL Server Agent may send e-mail from the address SqlAgent@Adventure-Works.com.

[ @display_name =] 'display_name'[ @display_name = ] 'display_name'
這個帳戶發出的電子郵件訊息所用的顯示名稱。The display name to use on e-mail messages from this account. display_namenvarchar (128),預設值是 NULL。display_name is nvarchar(128), with a default of NULL. 例如,帳戶 SQL ServerSQL Server代理程式顯示的名稱可能SQL Server Agent Automated Mailer電子郵件訊息上。For example, an account for SQL ServerSQL Server Agent may display the name SQL Server Agent Automated Mailer on e-mail messages.

[ @replyto_address =] 'replyto_address'[ @replyto_address = ] 'replyto_address'
這是在回應此帳戶的訊息時,回應的傳送地址。The address that responses to messages from this account are sent to. replyto_addressnvarchar (128),預設值是 NULL。replyto_address is nvarchar(128), with a default of NULL. 例如,回覆給帳戶 SQL ServerSQL Server代理程式可能會移至資料庫管理員, danw@Adventure-Works.comFor example, replies to an account for SQL ServerSQL Server Agent may go to the database administrator, danw@Adventure-Works.com.

[ @description = ] 'description'[ @description = ] 'description'
這是帳戶的描述。Is a description for the account. 描述nvarchar (256),預設值是 NULL。description is nvarchar(256), with a default of NULL.

[ @mailserver_name = ] 'server_name'[ @mailserver_name = ] 'server_name'
這個帳戶要用的 SMTP 郵件伺服器的名稱或 IP 位址。The name or IP address of the SMTP mail server to use for this account. 執行的電腦 SQL ServerSQL Server必須能夠解析server_name為 IP 位址。The computer that runs SQL ServerSQL Server must be able to resolve the server_name to an IP address. server_namesysname,沒有預設值。server_name is sysname, with no default.

[ @mailserver_type = ] 'server_type'[ @mailserver_type = ] 'server_type'
電子郵件伺服器的類型。The type of e-mail server. server_typesysname,預設值是 'SMTP'...server_type is sysname, with a default of 'SMTP'..

[ @port = ] port_number[ @port = ] port_number
電子郵件伺服器的通訊埠編號。The port number for the e-mail server. port_numberint,預設值為 25。port_number is int, with a default of 25.

[ @username =] 'username'[ @username = ] 'username'
用來登入電子郵件伺服器的使用者名稱。The user name to use to log on to the e-mail server. 使用者名稱nvarchar (128),預設值是 NULL。username is nvarchar(128), with a default of NULL. 當這個參數是 NULL 時,Database Mail 不會在這個帳戶上使用驗證。When this parameter is NULL, Database Mail does not use authentication for this account. 如果郵件伺服器不需要驗證,使用者名稱便使用 NULL。If the mail server does not require authentication, use NULL for the username.

[ @password =] '密碼'[ @password = ] 'password'
用來登入電子郵件伺服器的密碼。The password to use to log on to the e-mail server. 密碼nvarchar (128),預設值是 NULL。password is nvarchar(128), with a default of NULL. 除非指定了使用者名稱,否則,不需要提供密碼。There is no need to provide a password unless a username is specified.

[ @use_default_credentials =] use_default_credentials[ @use_default_credentials = ] use_default_credentials
指定是否要使用 SQL Server Database EngineSQL Server Database Engine 的認證將郵件傳送至 SMTP 伺服器。Specifies whether to send the mail to the SMTP server using the credentials of the SQL Server Database EngineSQL Server Database Engine. use_default_credentials bit,預設值是 0。use_default_credentials is bit, with a default of 0. 當此參數是 1 時,Database Mail 會使用 Database EngineDatabase Engine 的認證。When this parameter is 1, Database Mail uses the credentials of the Database EngineDatabase Engine. 此參數為 0 時,Database Mail 傳送@username@password參數如果有的話,否則會傳送不含@username@password參數。When this parameter is 0, Database Mail sends the @username and @password parameters if present, otherwise sends mail without @username and @password parameters.

[ @enable_ssl =] enable_ssl[ @enable_ssl = ] enable_ssl
指定 Database Mail 是否使用安全通訊端層加密通訊。Specifies whether Database Mail encrypts communication using Secure Sockets Layer. Enable_ssl bit,預設值是 0。Enable_ssl is bit, with a default of 0.

[ @account_id =] account_id輸出[ @account_id = ] account_id OUTPUT
傳回新帳戶的帳戶識別碼。Returns the account id for the new account. account_idint,預設值是 NULL。account_id is int, with a default of NULL.

傳回碼值Return Code Values

0 (成功) 或1 (失敗)0 (success) or 1 (failure)

備註Remarks

Database Mail 提供不同的參數@email_address@display_name,和@replyto_addressDatabase Mail provides separate parameters for @email_address, @display_name, and @replyto_address. @email_address參數是傳送訊息的位址。The @email_address parameter is the address from which the message is sent. @display_name參數是顯示的名稱從: 電子郵件訊息的欄位。The @display_name parameter is the name shown in the From: field of the e-mail message. @replyto_address參數是位址將會傳送回覆的電子郵件訊息。The @replyto_address parameter is the address where replies to the e-mail message will be sent. 例如, SQL ServerSQL Server Agent 所用的帳戶,可以從只供 SQL ServerSQL Server Agent 使用的電子郵件地址傳送電子郵件訊息。For example, an account used for SQL ServerSQL Server Agent may send e-mail messages from an e-mail address that is only used for SQL ServerSQL Server Agent. 這個地址所送出的訊息應該會顯示易記名稱,使收件者能夠輕鬆判斷是 SQL ServerSQL Server Agent 送出訊息。Messages from that address should display a friendly name, so recipients can easily determine that SQL ServerSQL Server Agent sent the message. 如果收件者回應訊息,這個回應應該會送給資料庫管理員,而不是 SQL ServerSQL Server Agent 所用的地址。If a recipient replies to the message, the reply should go to the database administrator rather than the address used by SQL ServerSQL Server Agent. 此案例中,此帳戶會使用SqlAgent@Adventure-Works.com為電子郵件地址。For this scenario, the account uses SqlAgent@Adventure-Works.com as the e-mail address. 顯示名稱設定為SQL Server Agent Automated MailerThe display name is set to SQL Server Agent Automated Mailer. 此帳戶會使用danw@Adventure-Works.com當做回應地址,因此回覆給由這個帳戶傳送的郵件移至資料庫管理員,而不是電子郵件地址 SQL ServerSQL Server代理程式。The account uses danw@Adventure-Works.com as the reply to address, so replies to messages sent from this account go to the database administrator rather than the e-mail address for SQL ServerSQL Server Agent. Database Mail 分別提供這三個參數的獨立設定,因此,您可以依照需要來設定訊息。By providing independent settings for these three parameters, Database Mail allows you to configure messages to suit your needs.

@mailserver_type參數支援值 'SMTP'The @mailserver_type parameter supports the value 'SMTP'.

@use_default_credentials是 1 時,郵件會傳送到 SMTP 伺服器使用的認證 SQL Server Database EngineSQL Server Database EngineWhen @use_default_credentials is 1 mail is sent to the SMTP server using the credentials of the SQL Server Database EngineSQL Server Database Engine. @use_default_credentials為 0 和@username@password指定帳戶時,此帳戶會使用 SMTP 驗證。When @use_default_credentials is 0 and a @username and @password are specified for an account, the account uses SMTP authentication. @username@password是此帳戶會使用 SMTP 伺服器,而沒有認證的認證 SQL ServerSQL Server或網路上的電腦。The @username and @password are the credentials the account uses for the SMTP server, not credentials for SQL ServerSQL Server or the network that the computer is on.

預存程序sysmail_add_account_sp處於msdb資料庫,擁有者是dbo結構描述。The stored procedure sysmail_add_account_sp is in the msdb database and is owned by the dbo schema. 此程序必須利用三部分名稱來執行,如果目前的資料庫不是msdbThe procedure must be executed with a three-part name if the current database is not msdb.

PermissionsPermissions

執行此程序預設值,成員的權限sysadmin固定的伺服器角色。Execute permissions for this procedure default to members of the sysadmin fixed server role.

範例Examples

下列範例會建立名稱為 AdventureWorks Administrator 的帳戶。The following example creates an account named AdventureWorks Administrator. 這個帳戶所用的電子郵件地址是 dba@Adventure-Works.com,它將郵件傳給 SMTP 郵件伺服器 smtp.Adventure-Works.comThe account uses the e-mail address dba@Adventure-Works.com and sends mail to the SMTP mail server smtp.Adventure-Works.com. 電子郵件傳送這個帳戶顯示AdventureWorks Automated Mailer從: 訊息。E-mail messages sent from this account show AdventureWorks Automated Mailer on the From: line of the message. 訊息的回應會導向 danw@Adventure-Works.comReplies to the messages are directed to danw@Adventure-Works.com.

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

另請參閱See Also

Database Mail Database Mail
建立 Database Mail 帳戶 Create a Database Mail Account
Database Mail 預存程序(Transact SQL)Database Mail Stored Procedures (Transact-SQL)