sp_OACreate Execute permission denied

4 Aug 2006 12:53 PM

Martin

Hi,
we are using sp_OACraete in a stored procedure to create an OLE-object
instance of an ActiveX-DLL. After migrating to SQLServer2005 we receive an

"Execute permission denied on object 'sp_OACreate' database
'mssqlsystemresource' schema 'sys'"

In Books online I get the Info "Requires membership in the sysadmin fixed
server role". But this seems not to be a good resolution. I can't hook up all
my users to a sysadmin!! So what should I do?? Thanks for all hints ....

Martin

4 Aug 2006 1:57 PM

David Browne

Show quote

"Martin" <martin@online.nospam> wrote in message
news:06DB6E12-9418-4941-B354-993A6956D621@microsoft.com...
> Hi,
> we are using sp_OACraete in a stored procedure to create an OLE-object
> instance of an ActiveX-DLL. After migrating to SQLServer2005 we receive an
>
> "Execute permission denied on object 'sp_OACreate' database
> 'mssqlsystemresource' schema 'sys'"
>
> In Books online I get the Info "Requires membership in the sysadmin fixed
> server role". But this seems not to be a good resolution. I can't hook up
> all
> my users to a sysadmin!! So what should I do?? Thanks for all hints ....
>

What _should_ you do? Stop using ActiveX dll's inside SQL Server. Instead
use CLR procedures instead. Much safer.

What _can_ you do? You can create the procedure with EXECUTE AS OWNER so it
runs as a priviliged SQL account.

David

4 Aug 2006 10:32 PM

Erland Sommarskog

Martin (martin@online.nospam) writes:

> we are using sp_OACraete in a stored procedure to create an OLE-object
> instance of an ActiveX-DLL. After migrating to SQLServer2005 we receive an
>
> "Execute permission denied on object 'sp_OACreate' database
> 'mssqlsystemresource' schema 'sys'"
>
> In Books online I get the Info "Requires membership in the sysadmin
> fixed server role". But this seems not to be a good resolution. I can't
> hook up all my users to a sysadmin!! So what should I do?? Thanks for
> all hints ....

Indeed, sp_OACreate is not for the security-aware...

As David said, move to CLR procedures if you can. I don't think his
suggestion to use EXECUTE AS OWNER is very good, though. (And it's
not sufficient anyway.) A better way is to embed the calls to
sp_OACreate in a proeedure signed with a certificate, and then grant
a user associate with that certificate the required permission. I have
an article on my web site which discusses this in detail:
http://www.sommarskog.se/grantperm.html.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

7 Aug 2006 7:52 AM

Martin

Hi Erland, hi David
thanks a lot for your help. you offered me 3 different ways to get happy
with my problem. I think I try to create a certificate for the procedure as
Erland described in his article (great article Erland). For security reasons
this seems to be the best way. As both of you mentioned in the next future it
will be the best to move the procedure to a CLR-procedure.

Thanks again ...

Martin
Show quote

"Erland Sommarskog" wrote:

> Martin (martin@online.nospam) writes:
> > we are using sp_OACraete in a stored procedure to create an OLE-object
> > instance of an ActiveX-DLL. After migrating to SQLServer2005 we receive an
> >
> > "Execute permission denied on object 'sp_OACreate' database
> > 'mssqlsystemresource' schema 'sys'"
> >
> > In Books online I get the Info "Requires membership in the sysadmin
> > fixed server role". But this seems not to be a good resolution. I can't
> > hook up all my users to a sysadmin!! So what should I do?? Thanks for
> > all hints ....
>
> Indeed, sp_OACreate is not for the security-aware...
>
> As David said, move to CLR procedures if you can. I don't think his
> suggestion to use EXECUTE AS OWNER is very good, though. (And it's
> not sufficient anyway.) A better way is to embed the calls to
> sp_OACreate in a proeedure signed with a certificate, and then grant
> a user associate with that certificate the required permission. I have
> an article on my web site which discusses this in detail:
> http://www.sommarskog.se/grantperm.html.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>