Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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 ;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft