Procedure with EXECUTE AS - Failing sp_send_dbmail

[Katerine]

SQL Server Standard 2008 R2

Kind of a weird issue. I have a couple of database procedures which call sp_send_dbmail. It worked last week. It doesn't work today. I did restart my computer between last week and today, and manually restarted the SQL Server (currently running: the DB Engine, Analysis Services, SQL Server Browser, Integration Services, and SQL Server Agent).

The login, "ProcessServeAllPermissions" is set up as a user in both this database and in the msdb database, and the user has been added to the role, "DatabaseMailUserRole".

Here's an example procedure:

CREATE PROCEDURE [dbo].[udp_SendWelcomeEmail]
	-- Add the parameters for the stored procedure here
	@NewUserID int
WITH EXECUTE AS 'ProcessServeAllPermissions'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @SubjectLine varchar(1000), @BodyContent varchar(max)
    DECLARE @UserPrimaryEmail varchar(200)
    DECLARE @MyName varchar(200), @MyEmail varchar(200)
    
    -- Insert statements for procedure here
    
-- Snipping all of the lines for setting the subject line, body content, primary email, and my email variables.
-- I've confirmed that the variables do end up getting set correctly. That's not the issue.

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'User Notification Emails', 
			@recipients = @UserPrimaryEmail,
			@reply_to = @MyEmail,
			@subject = @SubjectLine,
			@body = @BodyContent
    END
END

GO

I've also tried changing the EXECUTE AS statement to, "WITH EXECUTE AS OWNER", and setting up certificates by adapting the code found here:

http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

...with no luck. No matter what, I'm always getting this error message today:

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

The 'User Notification Emails' profile is still set up in Database Mail, and when I right-click Database Mail (in SSMS) and send a test email, it works.

Help, please!

Katie

 

[SQLBill]

Run this in MSDB and see if the login is returned.

EXEC sp_change_users_login 'report'

If it shows up, that means it got 'orphaned'. Run this to fix it..

EXEC sp_change_users_login 'auto_fix', 'ProcessServeAllPermissions'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Katerine]

Hi SQLBill,
Thanks for the response, and sorry it took a while to get back to this project. It didn't work, unfortunately. (That is, that apparently wasn't the problem).

Still having the issue. Have restarted my computer and the various SQL Services several times.

I do have a bit more information, though!I ran the following procedures - hopefully the results will give somebody more experienced than I a red flag!

EXEC msdb.dbo.sysmail_help_status_sp;
STARTED

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
mail	0	INACTIVE	2012-11-30 18:22:19.377	2012-11-30 18:37:33.590

SELECT * FROM msdb.dbo.sysmail_profile;
2	User Notification Emails	Welcome emails and "Forgot Password" emails - emails that tell the user basic info about their account.	2012-11-16 18:04:33.270	My-PC\Geek

SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems ;
1	2012-11-16 17:57:55.000
2	2012-11-16 18:31:00.000
2	2012-11-16 19:01:36.000
2	2012-11-19 15:09:27.000
2	2012-11-30 12:12:20.000

SELECT * FROM msdb.dbo.sysmail_event_log ;
1	information	2012-11-16 17:57:48.867	DatabaseMail process is started	4912	NULL	NULL	2012-11-16 17:57:48.867	NT AUTHORITY\NETWORK SERVICE
2	information	2012-11-16 18:07:49.463	DatabaseMail process is shutting down	4912	NULL	NULL	2012-11-16 18:07:49.463	NT AUTHORITY\NETWORK SERVICE
3	information	2012-11-16 18:30:54.673	DatabaseMail process is started	608	NULL	NULL	2012-11-16 18:30:54.673	NT AUTHORITY\NETWORK SERVICE
4	information	2012-11-16 18:40:54.723	DatabaseMail process is shutting down	608	NULL	NULL	2012-11-16 18:40:54.723	NT AUTHORITY\NETWORK SERVICE
5	information	2012-11-16 19:01:34.950	DatabaseMail process is started	4612	NULL	NULL	2012-11-16 19:01:34.950	NT AUTHORITY\NETWORK SERVICE
6	information	2012-11-16 19:11:35.010	DatabaseMail process is shutting down	4612	NULL	NULL	2012-11-16 19:11:35.010	NT AUTHORITY\NETWORK SERVICE
7	information	2012-11-19 15:09:25.687	DatabaseMail process is started	1892	NULL	NULL	2012-11-19 15:09:25.687	NT AUTHORITY\NETWORK SERVICE
8	information	2012-11-19 15:19:26.210	DatabaseMail process is shutting down	1892	NULL	NULL	2012-11-19 15:19:26.210	NT AUTHORITY\NETWORK SERVICE
9	information	2012-11-30 12:12:18.737	DatabaseMail process is started	5140	NULL	NULL	2012-11-30 12:12:18.737	NT AUTHORITY\NETWORK SERVICE
10	information	2012-11-30 12:21:03.837	The mail queue was started by login "My-PC\Geek".	NULL	NULL	NULL	2012-11-30 12:21:03.837	My-PC\Geek
11	information	2012-11-30 12:22:19.387	DatabaseMail process is shutting down	5140	NULL	NULL	2012-11-30 12:22:19.387	NT AUTHORITY\NETWORK SERVICE
12	information	2012-11-30 12:37:33.590	The mail queue was started by login "My-PC\Geek".	NULL	NULL	NULL	2012-11-30 12:37:33.590	My-PC\Geek

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
DatabaseMailUserRole	My-PC\Geek	0x010500000000000515000000EAA9862C24F7ED988C19031AF3030000
DatabaseMailUserRole	ProcessServeAllPermissions	0x735F231ED5FEA24795D7612110D7D68C
DatabaseMailUserRole	SendWelcomeEmailProcedure	0x0106000000000009010000009EA3D19A251FD355A031A3A67E4F33D7AF280C06

EXEC msdb.dbo.sysmail_help_principalprofile_sp ;
Returned an empty result set

EXEC sysmail_help_profileaccount_sp;
2	User Notification Emails	2	Process Server Website Setup	1

exec msdb.dbo.sysmail_help_principalprofile_sp
    @principal_name='ProcessServeAllPermissions', @profile_name='User Notification Emails';
Returned an empty result set

Many thanks again for any help!

Katie

 

[Katerine]

Sorry about the multiples; I just thought of one other thing that might or might not be relevant.

The procedures have "WITH EXECUTE AS 'ProcessServeAllPermissions'" so that they execute as ProcessServeAllPermissions (a SQL Server Authenticated account), regardless of who you're logged in as (provided that you have execute permissions on the procedure, of course). I'm testing while logged in as the owner (trusted connection), but it will also need to eventually work when called by another SQL Server login with limited permissions (for the website). Basically, the website will need to be able to run this procedure, but the website login should not have permissions to send mail willy-nilly, which is why I set it up this way.

Thanks again!

Katie

 

[Katerine]

Following up:
It appears that what happened is that originally, the database (the one that contains the stored procedure) was marked as trustworthy, and somehow trustworthy got turned off. (It's possible that I did that, and forgot; this is a development database so I've been trying a lot of things with it). As such, stored procedures within the database no longer had permission to execute msdb procedures, regardless of what the user permission said.

Turning Trustworthy back on fixed the problem, at least for now. At some later time, I'll probably try to get it working with certificates again, but I'm not terribly worried about Trustworthy (I'm the only developer, and I trust myself ).

Katie