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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@pcelba,
Option 3 also mentions I need to get mail server ports. In SSIS, I only setup SMTP using the server name.
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.
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
do you have any news about this subject? Does your SQL mail work already? If yes then we could close the question.
TIA
Pavel
ASKER
I went with pcelba's option 3 SP msdb.dbo.sp_send_dbmail. But I had to ask for permissions to use it. Thanks.
ASKER
I looked at option 3 but it mentions SQL Agent, which to me means full dba, which I am not.