Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail attachments Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 10:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:15 AM
Points: 224, Visits: 892
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'
Post #1522399
Posted Saturday, December 14, 2013 2:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
Maybe the user needs permission to folder where the attachment is located ??


Post #1522975
Posted Saturday, December 14, 2013 4:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522988
Posted Sunday, December 15, 2013 7:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:15 AM
Points: 224, Visits: 892
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'
Post #1523024
Posted Sunday, December 15, 2013 7:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523028
Posted Sunday, December 15, 2013 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 6,845, Visits: 13,379
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1523034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse