Prev: NOLOCK being blocked
Next: Syntax question in stored procedure
From: Kelly on 10 Oct 2009 01:17
Try adding the DatabaseMailUserRole role to your web user (not the user
you're logging into ssms with).

"Daniel" wrote:

> No..
>
> the problem i cant figure out is why the same exact code i posted below i
> have in 2 other procedures within the same database and the others work
> fine.. but this one is having issues..
> all 3 execute fine manually.. and 2 of the 3 work fine from the webpages..
>
> so dont think its login issue.. im sure something has happened to
> permissions.. we work on virtual machines for our development.. and have
> found that there are times that the VM is moved to another server and things
> changed outside of SQL that have caused problems before.. but this seems
> internal to SQL..
>
> how can i check the execute permissions of the sp_send_dbmail??
>
> EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb',
> schema 'dbo'.
>
> --
> ASP, SQL2005, DW8 VBScript, Visual Studio 2005, Visual Studio 2008
> "Linchi Shea" <LinchiShea(a)discussions.microsoft.com> wrote in message
> news:723FF1B0-A11C-4164-9A2C-234ABEC9C243(a)microsoft.com...
> >> If i execute the procedure manually within management studio it works
> >> fine,
> >> email is sent and record is updated..
> >
> > Did you use the same login (to log into the SQL instance) that the web
> > server was using?
> >
> > Linchi
> >
> > "Daniel" wrote:
> >
> >> Im getting this error when trying to execute my stored procedure that has
> >> code within to email
> >>
> >> EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb',
> >> schema 'dbo'.
> >>
> >> If i execute the procedure manually within management studio it works
> >> fine,
> >> email is sent and record is updated..
> >>
> >> We have other pages within our site that use similar procedures and they
> >> all
> >> work fine.. just dont know why this one isnt..
> >>
> >> here is the code within the procedure.. keeping in mind that it works if
> >> manually executed and does NOT if executed from the webpage.
> >>
> >> -- send the mail
> >>
> >> EXEC msdb.dbo.sp_send_dbmail
> >>
> >> @profile_name = 'CESync'
> >>
> >> ,@recipients = 'dcha(a)idge.com'
> >>
> >> ,@body = @tableHTML
> >>
> >> ,@subject = 'Credit Memo To Back Out'
> >>
> >> ,@body_format = 'HTML' ;
> >>
> >>
> >> --
> >> ASP, SQL2005, DW8 VBScript, Visual Studio 2005, Visual Studio 2008
> >>
> >>
> >>
>
>
>
 | 
Pages: 1
Prev: NOLOCK being blocked
Next: Syntax question in stored procedure