Re: Execute Persmission denied on object 'sp_OACreate'

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 02/05/05

  • Next message: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'" 
    Date: Sat, 5 Feb 2005 09:38:21 -0600

    > No job is invovled since the DTS package is being executed directly unless
    > SQL Server is creating a job behind the scenes. Is that the case?

    You are correct - no job is involved. It's just that the proxy account for
    non-sysadmin users is configured in the EM GUI under the Job System tab.
    The specified account is used as the OS security context for non-sysadmin
    users regardless of whether or not jobs are involved.

    > I am using Windows Authentication only and the SA account has no
    > permissions. The idea behind that was to prevent someone trying to guess
    > the
    > SA account password and gaining access to the database.

    The SQL authentication mode doesn't matter for ownership chaining purposes.
    A login can still own databases even when it can't be used to connect to SQL
    Server. 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message 
news:90F41D81-9174-4AFB-8504-0464C8249AEA@microsoft.com...
> Thanks for the reply.  I need to clarify . . .
>
> You wrote:
> You'll also need to specify
> the proxy account security context for non-sysadmin users from Enterprise
> Manager under SQL Server Agent --> Job System.
>
> No job is invovled since the DTS package is being executed directly unless
> SQL Server is creating a job behind the scenes.  Is that the case?
>
> I am using Windows Authentication only and the SA account has no
> permissions.  The idea behind that was to prevent someone trying to guess 
> the
> SA account password and gaining access to the database.
>
> Bill
>
> "Dan Guzman" wrote:
>
>> > How can get a user permissions to execute these stored procedures 
>> > without
>> > assigning the dbo role.  Surely I do not have to assign persmissions in
>> > the
>> > master database.
>>
>> SQL Server doesn't check permissions on indirectly referenced objects as
>> long as the ownership chain is unbroken.  For dbo-owned objects in 
>> different
>> databases, the databases involved need to be owned by the same user.  If 
>> you
>> are running SQL 2000 SP3+, the databases involved also need to have the 
>> 'db
>> chaining' option enabled.  See Cross Db Ownership Chaining
>> <adminsql.chm::/ad_config_8d7m.htm> in the SQL Server 2000 Books Online 
>> for
>> more information.
>>
>> You can prevent ad-hoc execution of powerful master database procs while
>> providing the needed functionality as follows.  Your user database needs 
>> to
>> be owned by 'sa' so that the ownership chain is unbroken for the 
>> dbo-owned
>> sp_OA* procs in the sa-owned master database.  You'll also need to 
>> specify
>> the proxy account security context for non-sysadmin users from Enterprise
>> Manager under SQL Server Agent --> Job System.  This account is used as 
>> the
>> OS security context for processes invoked by non-sysadmin users and 
>> should
>> have the minimum permissions needed by your application.
>>
>> It is important to note that you should enable 'db chaining' in an 
>> sa-owned
>> database when only sysadmin role members have permissions to create
>> dbo-owned objects in that database.  Also, ensure that parameters passed 
>> to
>> your proc is can't be used to inject unintended commands.
>>
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message
>> news:0D0422D2-0350-46B7-A963-7AAE1011EEC3@microsoft.com...
>> >I have a user who has execute permissions on a store procedure in a
>> >database
>> > which in turns executes 4 stored procedures in the master database. 
>> > These
>> > 4
>> > stored procedures execute a DTS package.
>> >
>> > Users, when given the dbo role, have no problem.  Users who only have 
>> > the
>> > Public role but execute permissions on the parent stored procedure get 
>> > the
>> > following error.
>> >
>> > Execute Persmission denied on object 'sp_OACreate', database 'master',
>> > owner
>> > 'dbo'
>> >
>> > How can get a user permissions to execute these stored procedures 
>> > without
>> > assigning the dbo role.  Surely I do not have to assign persmissions in
>> > the
>> > master database.
>> >
>> > Any help would be appreciated.
>> >
>> > WR
>> >
>>
>>
>>
  • Next message: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"

    Relevant Pages

    • ADP, Application Role, and objects
      ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
      (microsoft.public.access.adp.sqlserver)
    • Re: Execute Persmission denied on object sp_OACreate
      ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
      (microsoft.public.sqlserver.security)
    • RE: xp_cmdshell, ownership chaining, sql 2000
      ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
      (microsoft.public.sqlserver.programming)
    • xp_cmdshell, ownership chaining, sql 2000
      ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
      (microsoft.public.sqlserver.programming)
    • Re: Data migration questions?
      ... Use SSIS or the SqlBulkCopy class to import the data into SQL Server. ... Anyone who has read my books knows that I'm not in favor of including BLOBs in the database. ... "Mervin Williams" wrote in message ... should I use a DataSet to bring the data down to the local machine that will run the code and execute the transformation logic from it. ...
      (microsoft.public.dotnet.framework.adonet)