Hi
We have a system written in VBA that retrieves data from SQL Server, and then Excel 2000 formats the data as the customer requires. This report is then e mailed using sp_send_dbmail.
However, the SQL login that this application uses is set up to be a System Administrator, and it's fallen to me to have it use a login with less privaledges.
The account that I've chosen has been set up to have all relevant permissions to SQL server, in fact the result sets are returned to Excel with no problems whatsoever. The problem comes when the VBA attempts to connect to the SQL Server with the syntax for sp_send_dbmail, every attempt receives an Access Denied Error.
The login that I'm using is a windows login, and has all permissions to the directories that the attachments are saved in.
Testing the sp_send_dbmail call from SSMS while connected as the windows login results in a successful 'Mail Sent' result.
I have tried the above with a SQL login, but had other problems with file access, presumably caused by authentication problems.
Incidentally, the attachments are stored on a separate server to the SQL server, and the reports run on workstations.
At this point, I'm stuck. Any advice would be welcome!
Thanks