simon.murin (12/17/2009)
CREATE PROCEDURE [dbo].[TestSendMail].....
WITH EXECUTE AS <account with rights to send e-mails>
Wouldn't it be easier that way?
Have you tried it without TRUSTWORTHY ON? It won't work for a number of reasons. First the possible values for the EXECUTE AS clause are
EXECUTE AS Clause (Transact-SQL)
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }
You would have to specify a login_name, not a user_name because this stored procedure is in a User Database and the permissions needed to execute sp_send_dbmail are needed in msdb. The database user in a user database has no rights or permissions in another database, the login associated with that user does. TRUSTWORTHY ON would be required to make that work, which has additional ramifications security wise.
From the Article....
This allows for Impersonation across databases to gain access to resources in other databases on the server. It is not a secure method of solving the problem since having TRUSTWORTHY ON allows for other side effects. If the database owner is a sysadmin, then TRUSTWORTHY ON provides full access to the server. This flag is also set OFF whenever a database is attached or restored to SQL Server. This is an easy to miss item that can become problematic in the worst possible times, like during disaster recovery.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]