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

paultran00
paultran00 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have many options, e.g.
1) Ask for the permissions
2) Create client application which will check the table periodically and sends appropriate e-mails
3) Use database mail (see SP msdb.dbo.sp_send_dbmail) - more info here: https://www.brentozar.com/blitz/database-mail-configuration/

Author

Commented:
Hi pcelba,

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

Author

Commented:
@pcelba,

Option 3 also mentions I need to get mail server ports.  In SSIS, I only setup SMTP using the server name.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial