Link to home
Create AccountLog in
Avatar of paultran00
paultran00Flag for United States of America

asked on

SQL SERVER 2008R2 and xp_sendmail to send email getting error EXECUTE permission denied

Hi guys,

At my last company, in SQL SERVER2008R2, I had a stored procedure that sends an email by calling:

EXEC  master.dbo.xp_sendmail

BUT at this new company, I don't have permission and am getting the error:

The EXECUTE permission was denied on the object 'xp_sendmail', database 'mssqlsystemresource', schema 'sys'.

What are my alternatives?  
Here's what I need:  when a new record is inserted into a table, I need to be alerted via an email.  AT my last company, the INSERT TRIGGER would run a stored procedure that puts together the email then sends it out by making a call to xp_sendmail

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of paultran00

ASKER

Hi pcelba,

I looked at option 3 but it mentions SQL Agent, which to me means full dba, which I am not.
@pcelba,

Option 3 also mentions I need to get mail server ports.  In SSIS, I only setup SMTP using the server name.
Of course, to grant EXECUTE permission or to setup SQL Agent job is potentially dangerous and it is good security practice to disable such options.

This also means you need DBA assistance or you cannot use options 1) and 3) but you should still be able to create the client application. You may use whatever programming language of your choice which supports both e-mails sending and SQL Server connections.

SMTP port will tell your admin for sure. There are just a few ports used for SMTP but this is fully in admin hands who can set unobvious value.

BTW, to use trigger for mail sending is not good option as it makes the insert command much slower than it could be without it. To watch the table from outside is better option.
pcelba is right, definitely check on sp_send_dbmail.  xp_sendmail is obsolete, and the new company may have already phased it out.
Hi paultran00,

do you have any news about this subject? Does your SQL mail work already? If yes then we could close the question.

TIA
Pavel
I went with pcelba's option 3 SP msdb.dbo.sp_send_dbmail.   But I had to ask for permissions to use it.  Thanks.