sp_send_dbmail attachments

Question

Post reply

sp_send_dbmail attachments

david.alcock

SSCertifiable

Points: 6021
More actions
December 12, 2013 at 10:23 am

#301368

Very strange one which I cant see a definitive answer for, apologies if its around here already btw
I have a non sys admin user, trying to send mail with an attachment - attachment is not local. When its attempted we receive an access is denied message although if we alter his login to be in the sysadmin role it works fine. It also works fine if we remove the attachment line.
The proxy account is a local admin on the machine where the file resides, all msdb role membership is OK.
Am I missing something?!!?
Many thanks as always
D
'Only he who wanders finds new paths'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

Maybe the user needs permission to folder where the attachment is located ??

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

You might also setup the database to be owned by "SA" even if (hopefully) the SA account is disabled and then include an EXECUTE AS OWNER in the stored procedure the user is using to send the mail (and I'm not talking about sp_Send_DBMail itself).

If there is no such stored procedure and you're allowing the user to use sp_Send_DBMail directly, I'd have to call that a "security risk" and recommend that you setup such a stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.alcock SSCertifiable Points: 6021 More actions · Answer 3

Thanks for the replies!

Permissions are set up on the folder and yes Jeff, it is via a stored procedure and sa is disabled too 🙂

'Only he who wanders finds new paths'

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

david.alcock (12/15/2013)
Thanks for the replies!
Permissions are set up on the folder and yes Jeff, it is via a stored procedure and sa is disabled too 🙂

Good. Does the stored procedure have an EXECUTE AS OWNER and is the owner of the DB "SA"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 5

You're talking about a proxy account being local admin on the machine holding the file.

So I'd guess you're talking about a SQL agent job. Or am I missing something?

Is it possible to access the remote file using Windows (or the good old DOS prompt)?

My guess would be it's an issue that the user cannot acces the file from outside the local server.

How do you reference the file? Do you use UNC path?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ivanmleonb Newbie Points: 1 More actions · Answer 6

How can i sen mail without a sysadmin user?

Sql server throw the following error

[Microsoft][ODBC SQL Server Driver][SQL Server]The client connection security context could not be impersonated. Attaching files require an integrated client login(42000,22051)