Re: permissions required for executing CDOSys stored procedures

Hi Russell

Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!

But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.

Cheers
Neile

"Russell Fields" wrote:

Dan,

Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.

RLF
"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@xxxxxxxxxxxxxxxx
Perhaps the cross-database chaining (a.k.a. db_chaining) database option
is turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain
is unbroken. Users need execute permissions on only the user stored
procedure.

Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin
users be allowed to create dbo-owned objects in this scenario in order to
prevent elevation of privileges.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"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!




.

Relevant Pages

  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
  • Re: Transaction Oriented Architecture (TOA)
    ... OOP is not required to wrap SQL. ... of stored procedures to act as an API for application programmers ... writing code against the database. ... With a well-defined API in place, ...
    (comp.object)
  • Re: Get all stored procedures
    ... For SQLSERVER, we can use the following SQL statement to query the SP ... objects of a certain database: ... Microsoft Online Support ... | Thread-Topic: Get all stored procedures ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cannot Open SQL Server Table in Access.ADP File.
    ... I dont think that SQL Server will take 'veiw dependencies' into effect; ... Generate Script Wizard did not work on my original database, ... After fixing some of my tables and a few stored procedures and views, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Serious errors with Create view command
    ... However the REASON I thought it should work on 'opening' the database was ... Why should I have asssumed that an SQL statement which works ... Stored procedures are nothing more than procedures that are stored in the ... There's no database on earth that behaves that way! ...
    (microsoft.public.fox.helpwanted)