如何从 SQL Server 包含的数据库发送邮件？

以下解决方案涉及创建一个证书，以在模拟 msdb.dbo.sp_send_mail 的功能的包含数据库中签署新创建的存储过程。这在我的测试中取得了成功，希望它对你有用。

-----------------------------------------------------------------------------
--This example expects an empty directory called C:\ContainedDatabaseExample 
--Make sure no files exist in the target directory first
-----------------------------------------------------------------------------


USE [master]
GO
--Cleanup from previous runs of this process
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
--===========================================================================
--Create a new 'contained' database for testing
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Contained')
    DROP DATABASE [Contained]
GO
CREATE DATABASE [Contained]
 CONTAINMENT = PARTIAL
 ON  PRIMARY 
(
NAME = N'Contained', 
FILENAME = N'C:\ContainedDatabaseExample\Contained.mdf' , 
SIZE = 1024KB , 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB
)
 LOG ON 
(
NAME = N'Contained_log', 
FILENAME = N'C:\ContainedDatabaseExample\Contained_log.ldf' , 
SIZE = 1024KB , 
MAXSIZE = 1024GB , 
FILEGROWTH = 1024KB
)
GO
ALTER DATABASE [Contained] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Contained].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Contained] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Contained] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Contained] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Contained] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Contained] SET ARITHABORT OFF 
GO
ALTER DATABASE [Contained] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Contained] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Contained] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Contained] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Contained] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Contained] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Contained] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Contained] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Contained] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Contained] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Contained] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Contained] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Contained] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Contained] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Contained] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Contained] SET RECOVERY FULL 
GO
ALTER DATABASE [Contained] SET  MULTI_USER 
GO
ALTER DATABASE [Contained] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Contained] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Contained] SET DEFAULT_FULLTEXT_LANGUAGE = 1033 
GO
ALTER DATABASE [Contained] SET DEFAULT_LANGUAGE = 1033 
GO
ALTER DATABASE [Contained] SET NESTED_TRIGGERS = ON 
GO
ALTER DATABASE [Contained] SET TRANSFORM_NOISE_WORDS = OFF 
GO
ALTER DATABASE [Contained] SET TWO_DIGIT_YEAR_CUTOFF = 2049 
GO
ALTER DATABASE [Contained] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Contained] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Contained] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Contained] SET QUERY_STORE = OFF
GO
USE [Contained]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE [Contained] SET  READ_WRITE 
GO



--Use the 'contained' database
USE Contained
GO

--Create a new user in the contained database
CREATE USER [ContainedUser] WITH PASSWORD=N'BaconLover1', DEFAULT_SCHEMA=[dbo]
GO

--Create a new role that allows for easy additions of users to 
--execute the 'contained' database new email procedure
CREATE ROLE [SendMailFromContainedDatabase]
GO

--Add the new contained database user to the database role
ALTER ROLE [SendMailFromContainedDatabase] ADD MEMBER [ContainedUser]
GO

--Create a new procedure in the Contained database that 
--emulates all of the functionality of msdb.dbo.sp_send_dbmail
--My example below doesn't take any parameters and hardcodes
--enough information to send a test email.
--The actual procedure would need all of the normal parameters
--that msdb.dbo.sp_send_dbmail uses

CREATE PROCEDURE ContainedSendMail
    (@ValidEmailAddress varchar(100)) 
    WITH EXECUTE AS OWNER
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,@recipients = @ValidEmailAddress
        ,@subject = 'From Contained Database'
        ,@importance = 'High';
END
GO

--Create a certificate in the contained database that will be
--used to sign the new email stored procedure
CREATE CERTIFICATE [DatabaseMailFromContainedDatabases] 
    ENCRYPTION BY PASSWORD = 'Password#1234'
    WITH SUBJECT = 'DatabaseMailFromContainedDatabases';
GO

--Sign the new email stored procedure with the certification
ADD SIGNATURE TO OBJECT::[ContainedSendMail] 
BY CERTIFICATE [DatabaseMailFromContainedDatabases]
WITH PASSWORD = 'Password#1234';
GO

--Remove the PRIVATE KEY because it's not really needed
ALTER CERTIFICATE [DatabaseMailFromContainedDatabases] 
REMOVE PRIVATE KEY;
GO

--Grant permissions to the database role to execute the 
GRANT EXECUTE ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO
GRANT VIEW DEFINITION ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO

--Backup the newly created certificate from the contained database
BACKUP CERTIFICATE [DatabaseMailFromContainedDatabases]
TO FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--Now, we need to import the 'contained' certificate into master
USE master
go
IF (select Count(*) from sys.certificates where name = 'DatabaseMailFromContainedDatabases') = 1
    DROP CERTIFICATE DatabaseMailFromContainedDatabases;
create CERTIFICATE [DatabaseMailFromContainedDatabases]
from FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--We need to associate the new certificate with a server login
--and grant permissions
USE [master]
GO
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
CREATE LOGIN [DatabaseMailFromContainedDatabases]
FROM CERTIFICATE [DatabaseMailFromContainedDatabases];
GRANT AUTHENTICATE SERVER TO [DatabaseMailFromContainedDatabases];
GO

--Add the new login to the MSDB DatabaseMailUserRole 
USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailFromContainedDatabases')
    DROP USER [DatabaseMailFromContainedDatabases]
CREATE USER [DatabaseMailFromContainedDatabases] FOR LOGIN [DatabaseMailFromContainedDatabases]
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [DatabaseMailFromContainedDatabases]
GO

此时，您“应该”能够以“包含”数据库用户 ID（在上面的示例脚本中创建）连接并运行

exec ContainedSendMail @ValidEmailAddress = 'YourEmailAddress'

确保您提供有效的电子邮件地址。大约一分钟后，我收到了测试邮件。

我尝试了一点来复制您的场景，最后我设法使用 SMTP 有效地向自己发送了一封电子邮件，但我必须创建一个 SQL CLR 存储过程，如下所示：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Net;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CLR_SendEmail(string subject, string body, string to)
    {
    SmtpClient smtpClient = new SmtpClient();
    NetworkCredential basicCredential =
    new NetworkCredential("user", "passkey");
    MailMessage message = new MailMessage();
    MailAddress fromAddress = new MailAddress("[email protected]");

    smtpClient.Host = "smtp.server.com";
    smtpClient.UseDefaultCredentials = false;
    smtpClient.Credentials = basicCredential;

    message.From = fromAddress;
    message.Subject = subject;
    message.Body = body;
    message.To.Add(to);

    try
    {
        smtpClient.Send(message);
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send(ex.Message);
    }
        SqlContext.Pipe.Send("Sent" +Environment.NewLine);    
    }
}

在我意识到我需要在 Visual Studio 上声明目标数据库的包含状态之前，发布这个项目我有很多悲伤：

之后就一切顺利了：

VS 2015 上 SQL CLR 的好指南

我们最近遇到了这个问题，并做了下面的工作。

1. 创建一个 sql 登录并授予 dbmailuser 权限
2. 使用上面创建的登录凭据创建了一个带有 sql native 提供程序的链接服务器。例如：LS_DBMAIL
3. 使用包含的 db 中的链接服务器名称调用 sp 发送邮件过程..例如：exec LS_DBMAIL.msdb.dbo.sp_send_mail