Redigera

Dela via


sysmail_add_account_sp (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Creates a new Database Mail account holding information about an SMTP account.

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'

The name of the account to add. @account_name is sysname, with no default.

[ @email_address = ] 'email_address'

The e-mail address to send the message from. This address must be an internet e-mail address. @email_address is nvarchar(128), with no default. For example, an account for SQL Server Agent might send e-mail from the address SqlAgent@adventure-works.com.

[ @display_name = ] 'display_name'

The display name to use on e-mail messages from this account. @display_name is nvarchar(128), with a default of NULL. For example, an account for SQL Server Agent might display the name SQL Server Agent Automated Mailer on e-mail messages.

[ @replyto_address = ] 'replyto_address'

The address that responses to messages from this account are sent to. @replyto_address is nvarchar(128), with a default of NULL. For example, replies to an account for SQL Server Agent might go to the database administrator, danw@adventure-works.com.

[ @description = ] 'description'

A description for the account. @description is nvarchar(256), with a default of NULL.

[ @mailserver_name = ] 'server_name'

The name or IP address of the SMTP mail server to use for this account. The computer that runs SQL Server must be able to resolve the @mailserver_name to an IP address. @mailserver_name is sysname, with no default.

[ @mailserver_type = ] 'server_type'

The type of e-mail server. @mailserver_type is sysname, with a default of SMTP.

[ @port = ] port_number

The port number for the e-mail server. @port is int, with a default of 25.

[ @username = ] 'username'

The user name to use to log on to the e-mail server. @username is nvarchar(128), with a default of NULL. When this parameter is NULL, Database Mail doesn't use authentication for this account. If the mail server doesn't require authentication, use NULL for the username.

[ @password = ] 'password'

The password to use to log on to the e-mail server. @password is nvarchar(128), with a default of NULL. There's no need to provide a password unless a username is specified.

[ @use_default_credentials = ] use_default_credentials

Specifies whether to send the mail to the SMTP server using the credentials of the SQL Server Database Engine. @use_default_credentials is bit, with a default of 0. When this parameter is 1, Database Mail uses the credentials of the Database Engine. 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

Specifies whether Database Mail encrypts communication using Secure Sockets Layer. @enable_ssl is bit, with a default of 0.

[ @account_id = ] account_id OUTPUT

Returns the account ID for the new account. @account_id is int, with a default of NULL.

Return code values

0 (success) or 1 (failure).

Remarks

Database Mail provides separate parameters for @email_address, @display_name, and @replyto_address. The @email_address parameter is the address from which the message is sent. The @display_name parameter is the name shown in the From: field of the e-mail message. The @replyto_address parameter is the address where replies to the e-mail message will be sent. For example, an account used for SQL Server Agent might send e-mail messages from an e-mail address that is only used for SQL Server Agent. Messages from that address should display a friendly name, so recipients can easily determine that SQL Server Agent sent the message. If a recipient replies to the message, the reply should go to the database administrator rather than the address used by SQL Server Agent. For this scenario, the account uses SqlAgent@adventure-works.com as the e-mail address. The display name is set to SQL Server Agent Automated Mailer. 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 Server Agent. By providing independent settings for these three parameters, Database Mail allows you to configure messages to suit your needs.

The @mailserver_type parameter supports the value SMTP.

When @use_default_credentials is 1, mail is sent to the SMTP server using the credentials of the SQL Server Database Engine. When @use_default_credentials is 0 and a @username and @password are specified for an account, the account uses SMTP authentication. The @username and @password are the credentials the account uses for the SMTP server, not credentials for SQL Server or the network that the computer is on.

The stored procedure sysmail_add_account_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database isn't msdb.

Permissions

This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.

Examples

The following example creates an account named AdventureWorks Administrator. The account uses the e-mail address dba@adventure-works.com and sends mail to the SMTP mail server smtp.adventure-works.com. E-mail messages sent from this account show AdventureWorks Automated Mailer on the From: line of the message. Replies to the messages are directed to danw@adventure-works.com.

EXEC 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';