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.
To create a Database Mail profile using Transact-SQL
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).
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.
For each account, execute msdb.dbo.sysmail_add_profileaccount_sp to add the account to the profile.
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.
Example
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 ;
See Also
Tasks
How to: Create Database Mail Public Profiles (Transact-SQL)
Concepts
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)