Solved

Granting The EXECUTE permission for a stored procedure

Posted on 2008-06-26
12
1,070 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
12 Comments
 
LVL 13

Expert Comment

by:rickchild
Comment Utility
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
Comment Utility

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

Expert Comment

by:chapmandew
Comment Utility
you can put EXECUTE AS 'dbo' at the front of your trigger to get around this problem.
0
 
LVL 2

Author Comment

by:meteorelec
Comment Utility
it runs with a service accout,

How exactly do i go about this?
0
 
LVL 2

Author Comment

by:meteorelec
Comment Utility
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
Comment Utility
like this:

create trigger tr_name_mytrigger
on tablenamefor insert

as
execute as user = 'dbo'
begin
print 'test'
end
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 2

Author Comment

by:meteorelec
Comment Utility
appari,

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

Author Comment

by:meteorelec
Comment Utility
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
Comment Utility
0
 
LVL 2

Author Comment

by:meteorelec
Comment Utility
appari,

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

Expert Comment

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

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
sorry..

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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 article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now