Requirements for using Database Mail in Microsoft SQL Server

Sometimes it is necessary to send emails e.g. from a trigger or stored procedure. Which configurations have to be made in order to send emails via SQL Server?
Submitted by Christina Plössnig on Mon, 11/23/2015 - 22:43
1 answer

Configure Database Mail and Sending Mails from SQL Server

  1. Create a Database Mail Profile

    2. You can use the Wizard "Configure Database Mail" in the Microsoft SQL Server Management Studio (Node: Management -> Node: Database Mail -> context menu).

    See screenshot https://techscreen.tuwien.ac.at/sites/default/files/databasemail.png

  2. Enable Database Mail

    3. USE msdb;
    GO
    EXECUTE dbo.sysmail_start_sp;
    GO

  3. Test Database Mail configuration

    4. To verify that the database mail profile is configured properly you can select „Send Test E-Mail“ from the context menu above.

  4. Additionally the sql user has to have the role "DatabaseMailUserRole"


    5. USE [msdb];
    GO
    CREATE USER [userxy] FOR LOGIN [userxy];
    GO

    USE [msdb];
    GO
    EXEC sp_addrolemember N'DatabaseMailUserRole', N'userxy'
    GO

  5. Send Mail using sp_send_dbmail

    6. Example code:
    declare @msg varchar(500)
    set @msg = 'test email vom server, getriggert über sql query'
    exec msdb.dbo.sp_send_dbmail @recipients=N'c.ploessnig@irgendwas.at', @body=@msg, @subject='Test', @profile_name = 'anexia'

    For further specification and arguments see https://msdn.microsoft.com/de-de/library/ms190307(v=sql.120).aspx

Submitted by Christina Plössnig on Tue, 11/24/2015 - 00:51