SQL Server Database Mail发布到IIS后失效且数据插入失败咨询

问题核心原因说明 #

首先你无需在IIS管理器中查找SQL Server相关的SMTP配置项：你使用的是SQL Server自带的Database Mail能力，发邮件逻辑完全在SQL Server侧执行，和IIS的SMTP组件没有任何关联。

本地运行正常、部署到IIS后插入失败的根本原因是权限不匹配：

• 本地Visual Studio运行时，应用用你的个人Windows账号（或本地配置的高权限账号）连接SQL Server，该账号有执行sp_send_dbmail存储过程、访问业务表的权限
• 部署到IIS后，应用用IIS应用池的服务账号连接SQL Server，该账号权限不足导致触发器执行报错，而触发器和插入操作在同一事务中，触发器报错会触发事务回滚，最终表现为新记录无法插入。

解决步骤 #

1. 确认IIS侧连接SQL Server的账号 #

先在IIS中找到部署站点对应的应用池，打开「高级设置」查看「标识」字段，确认应用池运行账号；也可以临时在业务接口中执行以下语句，直接输出当前连接SQL的账号：

SELECT SUSER_SNAME()

2. 给对应账号授予必要权限 #

用SQL Server管理员账号登录，执行以下语句赋权：

-- 1. 将账号加入msdb库的DatabaseMail角色，获取发邮件权限
USE msdb
GO
CREATE USER [上一步查到的IIS连接账号] FOR LOGIN [对应的SQL登录名]
GO
EXEC sp_addrolemember 'DatabaseMailUserRole', [上一步查到的IIS连接账号]
GO

-- 2. 授予业务表查询、存储过程执行权限
USE [你的业务数据库名]
GO
GRANT SELECT ON dbo.[你的业务表名] TO [上一步查到的IIS连接账号]
GO
GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO [上一步查到的IIS连接账号]
GO

3. 触发器逻辑优化（可选，建议修改） #

你当前的触发器写法存在隐患，建议调整：

• 不要用SELECT MAX(Number)取最新编号，高并发场景下会取到其他会话插入的编号，改为直接从INSERTED系统表取当前插入的记录值：

  SELECT @bodyHtml = 'http:***************'+ CAST(Number AS nvarchar(max)) FROM INSERTED
  

• 你声明的@bodyHtml长度仅为VARCHAR(100)，链接+编号长度超过100会被截断，建议调整为VARCHAR(1000)或更大长度
• 建议把发邮件逻辑改为异步：触发器仅将邮件待发信息写入队列表，再用SQL Server代理作业定期扫表发邮件，避免邮件发送失败影响业务数据插入。

内容的提问来源于stack exchange，提问作者Julien7377