Solved

Granting The EXECUTE permission for a stored procedure

Posted on 2008-06-26
12
1,074 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 500 total points
ID: 21875559
sorry..

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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