Rights Required to Send Mail with Attachment in SQL Server 2005

Question

Post reply

Rights Required to Send Mail with Attachment in SQL Server 2005

pammu

Old Hand

Points: 374
More actions
April 3, 2012 at 5:19 am

#266492

Hi,
Kindly let me know what kind of rights are required to send_mail with attachment in sql server 2005.
SQL Server Login : TEST
Send mail Query:
USE msdb
EXEC sp_send_dbmail
@profile_name='MailProfile',
@recipients='login@gmail.com',
@subject='Test Attachment',
@body='This is a test mail send throguth Database mail. Thanks and Regards...pammu',
@file_attachments='D:\logo.jpg'
Thanks and Regards
pammu:-)

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

your user "TEST" needs to also be a user in the msdb database.

that user needs to be in the DatabaseMailUserRole which exists in that database.

something like this is the scripts you'd use:

USE msdb;

Create USER [Test] FOR LOGIN [Test]

EXEC sp_addrolemember N'DatabaseMailUserRole', N'Test'

the attachment must exist on the SQL Server itself and not the end users machine.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pammu Old Hand Points: 374 More actions · Answer 2

Thanks for your quick reply.

mentioned rights given to the user and after executed the send mail query i am getting the below mentioned error. May be it needs some more permissions.

Msg 22051, Level 16, State 1, Line 0

The client connection security context could not be impersonated. Attaching files require an integrated client login.

Thanks and Regards

pammu... 🙂

Lowell SSC Guru Points: 323482 More actions · Answer 3

pammu (4/3/2012)
Thanks for your quick reply.
mentioned rights given to the user and after executed the send mail query i am getting the below mentioned error. May be it needs some more permissions.
Msg 22051, Level 16, State 1, Line 0
The client connection security context could not be impersonated. Attaching files require an integrated client login.
Thanks and Regards
pammu... 🙂

so your attachment is using a UNC path, right? like \\SomeMachine\c$\Dir\my.pdf?

that requires that the user be a windows user, not a SQL user....or you need ato pass credentials to access the file, like a mapped drive with credentials included.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

pammu Old Hand Points: 374 More actions · Answer 4

No Lowell I am using the Direct server path. One more thing when i gave sysadmin privilege to this user the mail sent successfully but when i changed to limited privileges this will give the above error.

I gave the XP_CMDSHELL execute permission to this user and it will gives the output in query window but when i trying to executing the below send_mail query it gives the above mentioned error.

Query:

USE msdb

EXEC sp_send_dbmail

@profile_name='Mail Profile',

@recipients='mailaccount@gmail.com',

@subject='Test Attachment',

@body='This is a test mail send throguth Database mail. Thanks and Regards...pammu',

@file_attachments='C:\logo.jpg'

error

Msg 22051, Level 16, State 1, Line 0

The client connection security context could not be impersonated. Attaching files require an integrated client login

Thanks and Regards

pammu... 🙂

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

for non-sysadins you'll need one more configuration step:

--------------------------------------------------------------------------------

--- grant access to the profile to the DBMailUsers role

--- (this is only needed for non-sysadmins)

--------------------------------------------------------------------------------

exec msdb.dbo.sysmail_add_principalprofile_sp

@principal_name = 'TEST', --> user, group or 'public'

@profile_name = 'MailProfile',

@is_default = 1

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."