E-Mail Functionality in SQL Server 2005

Next, you have the option of configuring system parameters. Here, you can define the maximum file size and prohibited extensions. These configurations are also possible in the mail server. However, in SQL Server 2005 you have the option of configuring them profile-wise. Because of this, you have the option of selecting profiles depending on your requirements.

Mail profiles can be either public or private. For a private profile, Database Mail maintains a list of users that use the profile. For a public profile, members of the msdb database role DatabseMailUserRole can use the profile. Also, there can be a default public profile for the server and a default private profile for a user.

The above configuration can be also done via T-SQL scripts.

Use the sysmail_add_account procedure as follows to create a Database Mail account, using mail.dynanet.com as the mail server and dinesh@dynanet.com as the e-mail account:

EXECUTE msdb.dbo.sysmail_add_account_sp
     @account_name = 'Dinesh',
     @description = 'Dinesh Mail on dynanet.',
     @email_address = 'dinesh@dynanet.com',
     @display_name = 'Dinesh Asanka',
     @mailserver_name = 'mail.dynanet.com'

Use the sysmail_add_profile procedure to create a Database Mail profile called Dinesh Mail Profile:

EXECUTE msdb.dbo.sysmail_add_profile_sp
     @profile_name = 'Dinesh',
     @description = 'Dinesh Profile'

User the sysmail_add_profileaccount procedure to add the Database Mail account and Database Mail profile you created in previous steps.

  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
     @profile_name = 'Dinesh',
     @account_name = 'Dinesh',
     @sequence_number = 1

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
     @profile_name = 'Dinesh',
     @principal_name = 'public',
     @is_default = 1 ;

After configuring Database Mail, it should be tested using Database Mail's testing functionality. Select the Send Test E-Mail option. You can select the profile to test and send the mail and then verify.