sysmail_update_account_sp (Transact-SQL)

Syntax

sysmail_update_account_sp [ [ @account_id= ] account_id ] [ , ] [ [ @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 ,
    [ @no_credential_change = ] changes to username and password ,
    [ @enable_ssl= ] enable_ssl 

Arguments

• [ @account_id = ] account_id
  The account ID to update. account_id is int, with a default of NULL. At least one of account_id or account_name must be specified. If both are specified, the procedure changes the name of the account.

• [ @account_name = ] 'account_name'
  The name of the account to update. account_name is sysname, with a default of NULL. At least one of account_id or account_name must be specified. If both are specified, the procedure changes the name of the account.

• [ @email_address = ] 'email_address'
  The new e-mail address to send the message from. This address must be an internet e-mail address. The server name in the address is the server that Database Mail uses to send mail from this account. email_address is nvarchar(128), with a default of NULL.

• [ @display_name = ] 'display_name'
  The new display name to use on e-mail messages from this account. display_name is nvarchar(128), with no default.

• [ @replyto_address = ] 'replyto_address'
  The new address to use in the Reply-To header of e-mail messages from this account. replyto_address is nvarchar(128), with no default.

• [ @description = ] 'description'
  The new description for the account. description is nvarchar(256), with a default of NULL.

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

• [ @mailserver_type = ] 'server_type'
  The new type of the mail server. server_type is sysname, with no default. For SQL Server 2005 and later, only a value of 'SMTP' is supported.

• [ @port = ] port_number
  The new port number of the mail server. port_number is int, with no default.

• [ @username = ] 'username'
  The new user name to use to log on to the mail server. User name is sysname, with no default.

• [ @password = ] 'password'
  The new password to use to log on to the mail server. password is sysname, with no default.

• [ @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 service. use_default_credentials is bit, with no default. When this parameter is 1, Database Mail uses the credentials of the Database Engine. When this parameter is 0, Database Mail uses the @username and @password for authentication on the SMTP server. If @username and @password are NULL then it will use anonymous authentication. Consult with your SMTP administrator before specifying this parameter

• [ @no_credential_change = ] changes to username and password
  Specifies whether the existing credentials (username and password) are to be changed. When this parameter is 1, it indicates no credential changes, and @username and @password are not required. When this parameter is 0, it indicates credential changes, and @username and @password must be provided. This parameter is bit with 0 as the default.

• [ @enable_ssl = ] enable_ssl
  Specifies whether Database Mail encrypts communication using Secure Sockets Layer (SSL). Use this option if SSL is required on your SMTP server. enable_ssl is bit, with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

When both the account name and the account id are specified, the stored procedure changes the account name in addition to updating the information for the account. Changing the account name may be useful to correct errors in the account name.

The @no_credential_change parameter can be used to make changes other than username and password for an account that is using basic authentication to send mail. For example, you can enable SSL without having to provide the username and password by specifying @no_credential_change = 1.

The stored procedure sysmail_update_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 is not msdb.

Permissions

Requires membership in the sysadmin fixed server role.

Examples

A. Changing the information for an account

EXECUTE msdb.dbo.sysmail_update_account_sp
    ,@account_name = 'AdventureWorks Administrator'
    ,@description = 'Mail account for administrative e-mail.'
    ,@email_address = 'dba@Adventure-Works.com'
    ,@display_name = 'AdventureWorks Automated Mailer'
    ,@replyto_address = NULL
    ,@mailserver_name = 'smtp.Adventure-Works.com'
    ,@mailserver_type = 'SMTP'
    ,@port = 25
    ,@username = NULL
    ,@password = NULL
    ,@use_default_credentials = 0
    ,@enable_ssl = 0;

B. Changing the name of an account and the information for an account

EXECUTE msdb.dbo.sysmail_update_account_sp
    ,@account_id = 125
    ,@account_name = 'Backup Mail Server'
    ,@description = 'Mail account for administrative e-mail.'
    ,@email_address = 'dba@Adventure-Works.com'
    ,@display_name = 'AdventureWorks Automated Mailer'
    ,@replyto_address = NULL
    ,@mailserver_name = 'smtp-backup.Adventure-Works.com'
    ,@mailserver_type = 'SMTP'
    ,@port = 25,
    ,@username = NULL
    ,@password = NULL
    ,@use_default_credentials = 0
    ,@enable_ssl = 0;

C. Using @no\_credential\_change to change only SSL for an account

EXECUTE msdb.dbo.sysmail_update_account_sp
    ,@account_id = 125
    ,@enable_ssl = 1
    ,@no_credential_change = 1;

See Also

Reference

Concepts

Change History