Posted by Erland Sommarskog on 08/12/05 00:49

(Purple-Man@hotmail.com) writes:
> 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/dnsq....
>
> 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

Since you did not include the full link, I don't what what is said
on that page. I suspect that this is an issue with ownership chaining.
This was something that was changed in SP3 where a new configuraiton
option "Cross DB Ownership Chaining" was added, and this is by default
off. It's possible that changing this could help things.

But don't run away to do it! There are good reasons to have it off, and
there are also good reasons to be careful with access to sp_OACreate,
since that exposes the entire server with admin privileges.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]