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 very much.
Dave.

To paraphrase Books Online: "Only members of the sysadmin fixed server role
can execute "sp_OA" procedures."

You need appropriate privileges to execute these procedures.


ML

I read the article that you linked to and it does seem to say that you can
do that (wrap a procedure like xp_OACreate and as long as the owner has
permissions, the executer will work), but my experience has been different.
I actually had to grant permissions to EXECUTE those procedures to the user
(security UGH!!!).  The alternative I came up with was to write an extended
stored procedure that did what I needed it to do, TEST EXTENSIVELY, and then
grant the user permissions to that procedure.

I would be interested in knowing if the author of that article was just
plain wrong or if there is a method to encapsulate stored procedure
permissions (much like a view encapsulates table permissions).

Mike

<Purple-***@hotmail.com> wrote in message
Show quoteHide quote

Squeezing out more performance!!
Number of transactions
Data manipulation problem
Update using an Inner Join
Either/Or/Any query returning erroneous(?) results
SQLDistribution Initialize Error
Wildcard Parameters?
Performance - Left Outer Joins VS Updates
Difference of values from the same column
Is this possible with DTS?