Can normal users exec xp_sendmail? 2

[LonnieJohnson]

I have a stored proc that assigns a value to a field based on user input from an Access front end.

The last part of the stored proc sends an email if certain conditions are met.

It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[dbomrrsm]

Not an expert on this but could you not wrap your code in a SP and give uses access to the SP.

[blue]DBomrrsm[/blue]

 

[LonnieJohnson]

Not sure what you mean when you say "wrap your code in a store procedure".

I have a stored procedure that is doing other stuff and the code to send the mail is at the end of it.

Is that what you mean?

The user has permission to the main stored proc.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[gbaksh]

There are a few other things that you need to check for xp_sendmail which are as follows:

-- Makes sure that the profile that will be used to send the mail is part of the local administrators group on the sql server box
-- Make sure that the two services: MSSQL service and SQLServer Agent service are started up under the same profile that is used to send the email
-- Make sure that you've tested settings from an exchange client (such as Outlook etc.) to ensure that an email can be sent out.

I thought this may of some help in troubleshooting.

 

[LonnieJohnson]

Thank you gbaksh,

The second and the third things are ok. I think my problem lies with the first one. The user does not have permission to execute xp_sendmail. I can do the same input on my machine and it works as I have dbo and administrator rights.

The user gets the follow message...

EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)


ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[mrdenny]

The use needs to be granted access to execute the xp_sendmail procedure. This can be done in enterpise manager or query analyser via the grant command.

grant exec on xp_sendmail to bject

You will also need to setup the SQLAgentProxy account in the SQL Server Agent properties in Enterprise Manager.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[DrSimon]

I know it's a number of months later Denny, but you didn't get a star earlier and this is exactly the problem I was trying to solve today. So have a start on me.
Simon Rouse

 

[LonnieJohnson]

Actually Mr Denny you do deserve a star. I remember this and I was so caught up in solving it, I never got back and thanked you. I usually am pretty good about showing my appreciation.

Thanks. That process is working very well in the application I wanted it to.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[mrdenny]

excellent. Glad it's working for both of you.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)