meteorelec
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',sche ma'dbo'. ie_write: insert into scheme.opdetm failed 1,
What is the problem?
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'
What is the problem?
to execute sp_send_dbmail the user should be a member of DatabaseMailUser role.
you can put EXECUTE AS 'dbo' at the front of your trigger to get around this problem.
ASKER
it runs with a service accout,
How exactly do i go about this?
How exactly do i go about this?
ASKER
That is my trigger attached,
where exactly would i put the
EXECUTE AS 'dbo'
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
like this:
create trigger tr_name_mytrigger
on tablenamefor insert
as
execute as user = 'dbo'
begin
print 'test'
end
create trigger tr_name_mytrigger
on tablenamefor insert
as
execute as user = 'dbo'
begin
print 'test'
end
ASKER
appari,
how do i make the user a member od the databasemailuser role?
how do i make the user a member od the databasemailuser role?
ASKER
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
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
ASKER
appari,
how do i make the user member of DatabaseMailUser role??
how do i make the user member of DatabaseMailUser role??
use tempdb
go
sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = 'youraccountname'
go
sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = 'youraccountname'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.