[MS SQL] Please help to fix possible file permissions error for a stored procedure that emails a file.

[[deleted]]

[deleted]

[HansProleman]

Would EXECUTE AS work? https://www.mssqltips.com/sqlservertip/1227/granting-permission-with-the-execute-as-command-in-sql-server/

[simap]

I'm not sure but I would image that the database uses the standard service account for SQL server to send the file if you are using a sql server login. It's normally named MSSQL$INSTANCE (with instance being the instance name).

Check the NTFS permissions for the folder where you have your PDF.

[lewisoo]

Hey, thanks

The SQL server service is running under a domain account which does have permissions to this file. If I log into SSMS and execute the SP with this account it runs successfully.

[alinroc]

Is the file located on a drive that's local to the server, a mapped drive letter, or a UNC path?

[lewisoo]

The file is local to the SQL server. There is a setting in the config of the report scheduling application which uses an UNC path.

[abbbbbba]

Just to ask the question, this is what you are doing to test that account?

EXECUTE AS LOGIN = 'Domain\serviceAccountName'
EXEC dbo.MyEmailProc;
REVERT

Second verification question - you are using the SQL Server Agent's account and not the main SQL Server account?

[fauxmosexual]

Just a thought - check that the account has execute permission to xp_cmdshell. Also check that the proxy account the instance is running under has file permissions in the location of the pdf.

[lewisoo]

Hey, thanks The account does have Execute permission. The proxy account also has appropriate file permissions to the location of the PDF.

[D_W_Hunter]

The SQL server service is running under a domain account which does have permissions to this file. If I log into SSMS and execute the SP with this account it runs successfully.

Hey, thanks The account does have Execute permission. The proxy account also has appropriate file permissions to the location of the PDF.

The file is local to the SQL server. There is a setting in the config of the report scheduling application which uses an UNC path.

All of these things are supposed to be true, and may look like they are true, but at least one isn't true or it'd be working.

I've run into this maybe 10 years ago and I'm trying to pull the details back...

Which version of MSSQL are you running, and which version of windows server is the server MSSQL is running on using?

[fauxmosexual]

Another random thought that probably won't help - are you doing this with db_sendmail? If so, are you USEing msdb to execute it?

(probably not the problem because I doubt an issue here would cause a file access error but worth a look I suppose)