I am using Windows authentication to send mail using sp_send_dbmail. Sending the mail works if I do not try to add an attachment. It also works if I try to send the mail with a local attachment (on the SQL Server machine). It works for remote attachments (as \\server\share_name\file.pdf) *if* I am connected to SQL Server as a sysadmin (Windows logon in the sysadmin role). However, if I try send with a remote attachment as a non-administrative user (Windows logon not in the sysadmin role), the the attachment fails. The error message I recieve is "Failed to open attachment file '\\server\share\file.pdf'. Access denied."
It has nothing to do with the permissions on the file or remote server itself, as to prove this theory I put my windows test account into the sysadmin role on the development SQLServer. Doing this made the attachment work without making any changes on the remote server. This account has been in the DatabaseMailUserRole, and sending e-mail worked fine when the user was not an admin as long as I didn't try to add an attachment.
It would appear there is some SQL Server permission setting that I'm missing to allow any user to add remote attachments. Obviously I can't just give everyone sysadmin rights on the server in order to let them include attachments in e-mail. Can anyone shed some light on this?
I'm using SQL Server 2005 SP 1 and running on Windows 2003 Server.