I'm trying to send email using msdb.dbo.sp_send_dbmail by a SQL Server login.
It works fine if I'm not attaching anything. But if I attach anything I get the following error:
"The client connection security context could not be impersonated. Attaching files require an integrated client login"
however, I'm still getting the same error.
What is wrong? am I missing something? how can I fix it? how can I allow non-sysadmin SQL Server logins to send emails with attachment?
- The file is local and not on a network drive
- I have Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
I have also tried to wrap sp_send_dbmail into a stored procedure the EXECUTE AS the windows account which has all the permission on the file, it can execute the wrapping stored procedure, and it can execute successfully sp_send_dbmail with attachement ... I've tried something like:
EXECUTE AS LOGIN='MyMachine\SQLMailUser' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMail', @recipients = 'myEmail@myDomain.xyz', @body ='some text in email body', @subject = 'some text for the subject', @file_attachments='D:\test\test.txt', @body_format ='HTML';
but same error :(