Link to home
Start Free TrialLog in
Avatar of meteorelec
meteorelecFlag for Ireland

asked on

Granting The EXECUTE permission for a stored procedure

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?
Avatar of rickchild
rickchild
Flag of United Kingdom of Great Britain and Northern Ireland image

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.

to execute sp_send_dbmail the user should be a member of DatabaseMailUser role.
Avatar of chapmandew
you can put EXECUTE AS 'dbo' at the front of your trigger to get around this problem.
Avatar of meteorelec

ASKER

it runs with a service accout,

How exactly do i go about this?
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

like this:

create trigger tr_name_mytrigger
on tablenamefor insert

as
execute as user = 'dbo'
begin
print 'test'
end
appari,

how do i make the user a member od the databasemailuser role?
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
appari,

how do i make the user member of DatabaseMailUser role??
use tempdb
go
sp_addrolemember  @rolename =  'DatabaseMailUserRole',
    @membername =  'youraccountname'
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial