Post reply

How to send mail from a contained database is SQL Server using Database Mail?

  • March 24, 2017 at 2:24 am

    #322021

    How can I send mail with a user within a contained database is SQL Server using Database Mail? I tried using msdb.dbo.sp_send_dbmail but since any users local to the contained database do not see msdb, therefore msdb.dbo.sp_send_dbmail is unreachable as well. Then I tried creating certificates and signing procedures, first going straight to msdb and then re-routing the whole stuff through the master database (I know, it's not a 'very good idea') but even that does not work (I suppose it would break the containment, hence not allowed).

    Is there a way do that at all?

  • March 26, 2017 at 3:38 pm

    #1935001

    If the user on the partially contained database is a "SQL user mapped to a login" or a "Windows User", they both are still created mapped to a login. That login can still have a user in MSDB that is a member of the DatabaseMailUser database role and it can still call sp_send_dbmail. I just tried it and got it to work. I was not able to get a SQL User that did not have a mapped login to work however.

    Joie Andrew
    "Since 1982"

  • March 27, 2017 at 7:56 am

    #1935060

    The main problem was that mapping the user in the contained database to an SQL Server login was not an option, it had to be contained as well. Meanwhile, I got a working answer elsewhere that I'll share here in case others run into this.
    The not working solution or what I tried first is as described below, with some additional details:

    1. I created a certificate in the caller database which was backed up and used to create the same certificate in the msdb database by restoring it there. 
    2. A wrapper stored procedure was made in the caller database that was signed with said certificate and all the necessary permissions granted to required roles.
    3. And after that there was a user created from that certificate in msdb, which was permitted to call dbo.sp_send_dbmail in msdb. 
    4. Watched it miserably as it refused to work.

    The 3rd step was where the mistake was made. Instead of treading the direct route in trying connect the caller database with msdb I had to restore the certificate in master database instead, create a login using that certificate and then create a user to that login in msdb.

    Now it works as expected.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply