Share via

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

Use the Database Mail Configuration Wizard or the Database Mail stored procedures to create public Database Mail profiles. A public profile allows any user with access to the msdb database to send e-mail using that profile.

To create a Database Mail public profile using Transact-SQL

  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. Grant public access to the profile by executing msdb.sysmail_add_principalprofile_sp with 'public' as the @principal_name, or 0 as the @principal_id.


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 all users in the msdb database.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'AdventureWorks Public Account',
    @description = 'Mail account for use by all database users.',
    @email_address = '',
    @replyto_address = '',
    @display_name = 'AdventureWorks Automated Mailer',
    @mailserver_name = '' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AdventureWorks Public Profile',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AdventureWorks Public Profile',
    @account_name = 'AdventureWorks Public Account',
    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'AdventureWorks Public Profile',
    @principal_name = 'public',
    @is_default = 1 ;

See Also


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


Database Mail

Other Resources

New Profile (Database Mail)
Select Configuration Task (Database Mail)
Database Mail Configuration Wizard
sysmail_add_account_sp (Transact-SQL)
sysmail_add_principalprofile_sp (Transact-SQL)
sysmail_add_profile_sp (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance