SQL Server 2000 permissioning problems

[clint878]

I am having permissioning issues with the following SQL Server 2000 stored procedure:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [EMail] @From varchar(100) , @To varchar(100) , @Subject varchar(100)=" ", @Body varchar(4000) =" " /******************************************

This stored procedure takes the parameters and sends an e-mail. All the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. References to the CDOSYS objects are at the following MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/ library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail1.traffic.com'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


The procedure is called from a trigger. The trigger attempts to send an E-Mail when a very specific case, involving changes and deletions in many tables, happens. The procedure runs fine when I test it.

When it was released to the users, however, the .NET application throws the following exception when rows are deleted to cause the E-Mail procedure to execute:


EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.


From what I can tell, the procedure sp_OACreate would cause a security hole if I just opened it to all users because they would be able to execute arbitrary code. First, is this assumption about security true?

Second, if simply granting permission to this procedure is unsafe, is there any way to allow the nested procedures to be used only in this context? That is, would it be possible to grant some sort of permission to allow sp_OACreate (and the other master database procedures in this example) to be called only from within E-Mail, and only with these parameters?

Thanks,

-Steve