Skip to content

Setting up SQL Server Database Mail

    1. Enable Database Mail for this instance
    EXECUTE sp_configure 'show advanced', 1;
    RECONFIGURE;
    EXECUTE sp_configure 'Database Mail XPs',1;
    RECONFIGURE;
    GO

    2. Create a Database Mail account

    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL',
    @description = 'Account used by all mail profiles.',
    @email_address = '[email protected]', -- enter your email address here
    [email protected]_address = '[email protected]', -- enter your email address here (note reply has been commented out as i had issues with setting up msn account)
    @display_name = 'Database Mail',
    @mailserver_name = 'smtp.live.com'; -- enter your server name here

    3. Create a Database Mail profile

    EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Default Public Profile',
    @description = 'Default public profile for all users';

    4. Add the account to the profile

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

    5. Grant access to the profile to all msdb database users

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

    6. Send a test email

    EXECUTE msdb.dbo.sp_send_dbmail
    @subject = 'Test Database Mail Message',
    @recipients = '[email protected]', -- enter your email address here
    @query = 'SELECT @@VERSION'; -- gives you the version of SQL
    
    GO
    
    View information about mail in msdb database using the following scripts:
    
    use msdb
    go
    
    SELECT * FROM sysmail_server
    SELECT * FROM sysmail_allitems 
    SELECT * FROM sysmail_sentitems
    SELECT * FROM sysmail_unsentitems 
    SELECT * FROM sysmail_faileditems 
    SELECT * FROM sysmail_mailitems
    SELECT * FROM sysmail_log
    

    To create a Database Mail profile, we can use either the Database Mail Configuration Wizard or T-SQL code. Using Configuration Wizard is easier, but bear in mind that Database Mail is turned off in SQL Server Express editions.

    Usually, all we need to do is go in Object Explorer, connect to the SQL Server instance we want to configure Database Mail on and expand the server tree. Then expand the Management node and double-click Database Mail or right-click and choose Configure Database Mail to open the Database Mail Configuration Wizard: