Purple-Man,

You login must have SA privileges to execute those sp_OA... stored
procedures.

Ilya

[quoted text, click to view]

I wanted to use sp_OACreate, sp_OAMethod and sp_OADestroy to execute a
DTS package from a stored procedure. I had the dba (using the sa
account) create a wrapper stored procedure as recommended in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_10182004.asp.
However, when I executed the wrapper stored procedure, I still
received privilege errors from the underlying sp_oa extended stored
procedures.

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 6
EXECUTE permission denied on object 'sp_OACreate', database 'master',
owner 'dbo'.

Any ideas on what we could be doing wrong, or any suggested resolutions
would be appreciated.

Thank you.

I am confused. Why would MSDN recommend we wrap them in a wrapper
stored procedure if we still need SA privileges to execute the
underlying extendible stored procedures? Their article implies that if
you are granted execute privileges to the wrapper, you should be OK.
Here is an excerpt from that document:

There are a few T-SQL commands and extensions that present their own
unique security concerns. One of these is sp_OACreate and its related
family of system procedures (e.g., sp_OAMethod, sp_OAProperty, etc.).
Earlier, we looked at a potential security problem that would be
created by granting an application login direct access to these
procedures. To avoid this problem, never write application code that
directly calls the sp_OA procedures. Instead, wrap all references to
these procedures in your own T-SQL stored procedures, and only grant
access to these wrapper stored procedures. Also, do not allow the
application code to pass in the names of COM objects or methods as
strings that are blindly invoked by the wrapper procedure.


Thank you.

I am confused. Why would MSDN recommend we wrap them in a wrapper
stored procedure if we still need SA privileges to execute the
underlying extendible stored procedures? Their article implies that if
you are granted execute privileges to the wrapper, you should be OK.
Here is an excerpt from that document:

There are a few T-SQL commands and extensions that present their own
unique security concerns. One of these is sp_OACreate and its related
family of system procedures (e.g., sp_OAMethod, sp_OAProperty, etc.).
Earlier, we looked at a potential security problem that would be
created by granting an application login direct access to these
procedures. To avoid this problem, never write application code that
directly calls the sp_OA procedures. Instead, wrap all references to
these procedures in your own T-SQL stored procedures, and only grant
access to these wrapper stored procedures. Also, do not allow the
application code to pass in the names of COM objects or methods as
strings that are blindly invoked by the wrapper procedure.


Thank you.