Re: permissions required for executing CDOSys stored procedures

Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.

The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)

RLF
"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:OdHHkzqrHHA.3492@xxxxxxxxxxxxxxxxxxxxxxx
Neile,

I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a
green check box next to it.

What was the response to: sp_helprotect @name='sp_OACreate'

Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin
server role on the SQL Server database unless steps are taken to prevent
that. For example, as described in
http://support.microsoft.com/kb/263712/.

RLF

"Neile Bermudes" <NB@xxxxxxxxxxxxxxxx> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@xxxxxxxxxxxxxxxx
Hi Russell,

Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears
that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.

Is this possible??

Thanks

"Russell Fields" wrote:

Neile,

I usually use sp_helprotect to investigate these things. E.g.

sp_helprotect @name='sp_OACreate'

I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a
permissions
page that shows this also, at least in SP2. With SQL Server 2000
Enterprise
Manager there is a permissions button on the properties form that serves
a
similar purpose.

RLF

"Neile Bermudes" <NB@xxxxxxxxxxxxxxxx> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@xxxxxxxxxxxxxxxx
Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question....
bizarre. I
assume the way to check this is by going into the procedure properties
screen
and clicking on the permissions tab? Is there another view somewhere
else??

thx

"Russell Fields" wrote:

Neile,

Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.

RLF
"Neile Bermudes" <NB@xxxxxxxxxxxxxxxx> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@xxxxxxxxxxxxxxxx
Hi there

I'm doing some analysis on the database applications in my
organisation
before migrating the databases to a new server. One of these makes
use
of
CDOSys objects for sending mail, instead of SQL Mail. There are a
number
of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can
execute
these stored procedures, however, the sql login for this
application is
not a
sysadmin! I thought perhaps there was a mistake in books online but
i've
looked on google and the permission requirements are the same -
must be
sysadmin. Any ideas how it still manages to function without these
rights?

Thanks in advance!










.