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.


Pages: 1 2 3 4 5


One Response to “E-Mail Functionality in SQL Server 2005”

  1. Dinesh,

    Thanks you very much for this article.
    I was able to set up a email account and send some test mails.
    Few questions:

    1) The profile that i created is public bit i didn’t made it default since my server has many databases and i will be using the mail profile for only one database.
    So should i make it private or leave it public(not default) ?

    2) With every schedule maintenance activity
    like database refresh or database shutdown\restart, do i need to manually check for the profile that i created still exists or not ?


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |