Permissions on a SP

I have created and tested two triggers directly on the local server
and both work as they should. I initially had a problem with
xp_smtp_sendmail that was returning a 500.1
Internal server permissions error, I corrected this with applying the
correct permissions.
Both triggers work when activated from a direct logon but fail without
any error message when activated from the internet. Do I need to create
the necessary permissions on more stored procedures, or can I recreate a
SP from the master DB and copy it, if someone could point me in the right
direction it would be greatly appreciated.
JohnDon't send email from within a trigger. There is really no need to hold open
a transaction while you do this and you don't want to block other
transactions if the XP is slow in returning control to the trigger. Also, yo
u
want to avoid giving users the ability to run XPs in Master, even indirectly
,
if you can. The lack of execute permissions is what is causing this error
message.
Since Email is an inherently asynchronous medium it is easier and better to
schedule this with a SQL Agent based job running at suitable intervals.
Record whatever changes are required in a table and then use a job to pick
those up and send emails.
David Portas
SQL Server MVP
--|||Thanks David for the sound advice.
John
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:10552FB0-890C-4036-80CD-CE40EE612444@.microsoft.com...
> Don't send email from within a trigger. There is really no need to hold
> open
> a transaction while you do this and you don't want to block other
> transactions if the XP is slow in returning control to the trigger. Also,
> you
> want to avoid giving users the ability to run XPs in Master, even
> indirectly,
> if you can. The lack of execute permissions is what is causing this error
> message.
> Since Email is an inherently asynchronous medium it is easier and better
> to
> schedule this with a SQL Agent based job running at suitable intervals.
> Record whatever changes are required in a table and then use a job to pick
> those up and send emails.
> --
> David Portas
> SQL Server MVP
> --