allowing non sysadmin user to send email with attachments

i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?

any suggestion will be appreciated. thanks.

Which version of SQL Server are you using?|||i'm using sql server 2005 sp2|||

One possibility could be using impersonation (EXECUTE AS) on the module and/or digital signatures. The idea would be to create a wrapper under your control that would escalate the privileges and call sp_snd_dbmail.

Personally I am not quite familiar with the DB mail functionally, but based on the fact that the invocation is via a SP, I am not sure if digital signatures will be enough in this particular case.

Digital signatures should allow the caller to execute the module, but the signature would be dropped in order to execute the code on sp_send_dbmail; if the SP has internal checks to verify the calling context the call will be terminated. Impersonating a privileged user (member of sysadmin in this case) and using a digital signature to vouch for the token at server scope (the signing certificate would require AUTHENTICATE SERVER) should be an option if the digital signature by itself is not enough in this scenario.

I would like to emphasize that because the escalation (either via signature or via EXECUTE AS) is going to be to sysadmin, you need to be extremely careful and make sure your code is safe and there are no possibilities of running arbitrary code (i.e. no SQL injection is possible, validate any input, make sure the parameters to sp_send_dbmail are safe, limit the escalated functionality to the essential minimum, etc.) by anyone who has permission to execute this module.

For more detailed information please refer to BOL:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

http://msdn2.microsoft.com/en-us/library/ms345102.aspx

Let us know if you have further questions or comments

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I forgot to include Laurentiu's article on cross-DB as a reference: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine