xp_startmail fails for non sa users

[dougjohnson3]

I have a project that requires using a trigger to send email (via Great Plains Dynamics Accounting). It invokes

xp_startmail. SQL Server 2000 runs in Mixed Mode. The program works ok if I login to the db using an sa accout.

Any Dynamics user can then trigger the sending of emails no matter which user I switch to after logging in. When I

login to the Dynamics SQL db using a normal user, Dynamics issues an Error and fails to send emails.

Below you will find the only part of the code that fails under those conditions that I tested by itself. The error

occurs when executing xp_startmail.

Any suggestions are much appreciated.


IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'nsi_trigger_sopOrderAcknowledgementByEmail')
DROP TRIGGER nsi_trigger_sopOrderAcknowledgementByEmail
GO
CREATE TRIGGER nsi_trigger_sopOrderAcknowledgementByEmail
ON dbo.SOP10100
-- NSI--- WITH ENCRYPTION
FOR UPDATE
AS
-- NSI--- IF UPDATE(CUSTNMBR)
IF UPDATE(CSTPONBR)
BEGIN
DECLARE @CompanyName varchar(30),
-- ...
@nsi_testvar char(15)

-- removed extraneous code...
-- ...

BEGIN

-- Run Outlook using an existing instance if open already...
-- EXEC master..xp_cmdshell 'C:\Program Files\Microsoft Office\Office10\Outlook.exe /Recycle'


--
-- ***Code throws errors here >>>
EXEC master..xp_startmail


-- EXEC master..xp_sendmail @recipients = 'dougjohnson3@netscape.net',
-- @subject = @nsi_subject,
-- @message = @nsi_emailMessage
-- EXEC master..xp_stopmail

END

END
GO

USE master
GRANT EXEC ON master..xp_cmdshell TO DYNGRP
GRANT EXEC ON master..xp_startmail TO DYNGRP
GRANT EXEC ON master..xp_sendmail TO DYNGRP
GRANT EXEC ON master..xp_stopmail TO DYNGRP
GO

[skhanal]

Have you configured sql server to allow non-sa to call command shell?. Go to server agent properties and assign a proxy account to use for non-sa users.