Re: permissions required for executing CDOSys stored procedures

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: SQL 2005 express security issue
    ... A system administrator such as 'sa' or any other login granted the sysadmin ... sysadmins then you can protect databases, tables, stored procedures, etc. ... However, within a database there are other roles that, if granted, give ... To understand what is revealed and to whom here is a write-up from SQL 2000: ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... We broke our ownership chains many years ago, ... Note that the user database must be owned by 'sa' to maintain an unbroken ... SQL Server MVP ... Apparently only members of the sysadmin role can ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... 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 ... well before SQL 2000 SP3, by giving each database a different owner. ... Apparently only members of the sysadmin role can ...
    (microsoft.public.sqlserver.security)
  • Re: sql2k5 security
    ... you could give a user only Execute permissions on a ... stored procedure and the stored procedure could perform operations fine. ... Create a table in the database. ... Create a Schema named mySchema in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: E mail notification from SQL Server
    ... Permissions on the Database Mail setup procs default to only sysadmin server role members. ... Execute permissions on the sp_send_dbmail default to members of the DatabaseMailUser msdb database role. ...
    (microsoft.public.sqlserver.programming)