Solved

Granting The EXECUTE permission for a stored procedure

Posted on 2008-06-26
12
1,072 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
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2005 Srink database in chunks 4 65
SQL help 5 56
Impove long SQL Stored Procedure Performance 14 78
Error when saving to sql table a '/' 5 29
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …

860 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