xp_sendmail with email attachments.

[jjjax0330]

We are trying to use the xp_sendmail procedure in sql 2005 to send some emails that have attachments. We are getting message about access to attachment file(s) denied when users run this. It works fine when on with admin and we know the users do have access to the attachment drive. It also seems to work when not sending attachments. I have found some information that mentions they need to be member of the sql sysadmin group to send? It does work when I add them to that group but just wondering what that may open up if I do so. Thanks, Joe

 

[mrdenny]

Are the attachments on a network share or on the SQL Server's hard drive?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jjjax0330]

On a network share.

 

[mrdenny]

That may be the problem. I've seen problems when using native SQL Server procedures accessing files on remote servers when not logged into the SQL Servers console.

You are probably being effected by the NTLM double hop issue.

The reason that it works for sysadmins is because when sysadmins access network resources the user uses the SQL Servers account. When non-sysadmins access network resources the SQL Server impersonates the users account.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jjjax0330]

Thanks. I tried doing from a network share on the sql server itself and that worked so we can probably work with that. Must be something that changed with sql 2005 upgrade because they tell me that this worked under 2000. Thanks again, Joe

 

[mrdenny]

Yes, this security change did comes with SQL 2005.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com