February 16, 2009 at 9:54 am
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
March 6, 2009 at 4:01 am
SQL Logins/Users do not exist outside the SQL Server instance, therefore have no access to the "outer world".
In order for SQL Users to access external resources they must use appropriate credentials provided by the operating system (usually via a Windows user account).
Look up the CREATE CREDENTIAL statement in Books Online.
You're not stuck, just very secure. 😉
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply