Re: Execute Persmission denied on object 'sp_OACreate'

From: WhiskyRomeo (WhiskyRomeo_at_discussions.microsoft.com)
Date: 02/05/05 

Date: Sat, 5 Feb 2005 07:09:01 -0800

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

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 is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Security question ..
    ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
    (microsoft.public.sqlserver.server)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)