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
paultran00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paultran00Author Commented:
Hi pcelba,

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

Option 3 also mentions I need to get mail server ports.  In SSIS, I only setup SMTP using the server name.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
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.
0
Scott PletcherSenior DBACommented:
pcelba is right, definitely check on sp_send_dbmail.  xp_sendmail is obsolete, and the new company may have already phased it out.
0
pcelbaCommented:
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
0
paultran00Author 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.