Export (0) Print
Expand All
2 out of 3 rated this helpful - Rate this topic

How to: Create Database Mail Private Profiles (Transact-SQL)

Use the Database Mail Configuration Wizard or the Database Mail stored procedures to create private Database Mail profiles for sending Simple Mail Transfer Protocol (SMTP) mail. A private profile can be used by an msdb database user or by the public role.

  1. Create one or more Database Mail accounts for the profile. For more information about creating Database Mail accounts, see How to: Create Database Mail Accounts (Transact-SQL).

  2. Execute the stored procedure msdb.dbo.sysmail_add_profile_sp to create the profile, specifying:

    • The name of the profile to create.
    • An optional description of the profile.
  3. For each account, execute msdb.dbo.sysmail_add_profileaccount_sp to add the account to the profile.

  4. For each database role or user that will send mail using this profile, grant access to the profile by executing msdb.sysmail_add_principalprofile_sp.

The following example creates a Database Mail account and a Database Mail profile. The example then adds the account to the profile and grants access to the profile to the ApplicationUser database user you create 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 ;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.