Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Granting The EXECUTE permission for a stored procedure

Posted on 2008-06-26
12
Medium Priority
?
1,081 Views
Last Modified: 2008-06-26
I have a stored procedure inside a insert trigger, when i do an insert from the console on the sql server management studio the insert runs ok and the trigger sets of the stored procedure (which happens to be sending an email), which all works fine,

the problem i'm having is when front end of the erp, what happens is i get an error message with says the following [Microsoft][SQL Native Client][SQL Server]The EXECUTE permission was denied on the object 'sp_send_dbmail',database'msdb',schema'dbo'. ie_write: insert into scheme.opdetm failed 1,

What is the problem?
0
Comment
Question by:meteorelec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21875050
How is the security set up on your application, is it using Windows Authentication of whoever runs the application, or is it set up to run with a service account?

If you application is running with a service, and this service is logged on to something like NTAuthority then you may need to change this to a Domain account, and grant EXEC on the SP to that domain account.
0
 
LVL 39

Expert Comment

by:appari
ID: 21875236

to execute sp_send_dbmail the user should be a member of DatabaseMailUser role.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21875244
you can put EXECUTE AS 'dbo' at the front of your trigger to get around this problem.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:meteorelec
ID: 21875259
it runs with a service accout,

How exactly do i go about this?
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21875319
That is my trigger attached,

where exactly would i put the

EXECUTE AS 'dbo'

DECLARE @Cost FLOAT
DECLARE @sql VARCHAR(1000)
 
SELECT @Cost = [cost]from inserted with (NOLOCK)
SELECT @sql = 
'SELECT ' 
+ cast(i.order_no as varchar(100)) + ' OrderNo ,' 
+ cast(i.order_line_no as varchar(100)) + ' OrderLineNo ,'
+ '''' + replace(d.product , '''', '''''') + ''' product '
from inserted i
JOIN scheme.opdetm d
ON i.order_no = d.order_no
AND i.order_line_no = d.order_line_no
 
IF @Cost=0
 
BEGIN 
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = '1',
@recipients = 'eoin.conway@meteorelectrical.com',
@subject = 'Product At Zero Cost- Immediate Attention Needed
',
@body = 'Please Give Product Relevant Cost Immediately',
@query = @sql,
@execute_query_database = 'demo'
 
END

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21875320
like this:

create trigger tr_name_mytrigger
on tablenamefor insert

as
execute as user = 'dbo'
begin
print 'test'
end
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21875356
appari,

how do i make the user a member od the databasemailuser role?
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21875434
chapmandew,

its now saying,

[Microsoft][SQL Native Client][SQL Server] cannot execute at the database principal because "dbo" does not exist, this type of principal cannon be impersonated, or you do not have permission. ie_write: insert into scheme.opdetm failed 1
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21875488
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21875516
appari,

how do i make the user member of DatabaseMailUser role??
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21875558
use tempdb
go
sp_addrolemember  @rolename =  'DatabaseMailUserRole',
    @membername =  'youraccountname'
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21875559
sorry..

use msdb
go
sp_addrolemember  @rolename =  'DatabaseMailUserRole',
    @membername =  'youraccountname'
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question