Re: Execute Persmission denied on object 'sp_OACreate'

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

  • Next message: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'" 
    Date: Sat, 5 Feb 2005 08:53:02 -0800

    This has been very helpful. I see via Books Online and Job System tab how
    you specify the proxy account; but, is this an account that has login and
    permissions to what?

    In my case, do I have to specify a windows account, give it a login, and
    give it access and permissions. If so what access and permissions.

    The Books Online does not explain.
    wr
    "Dan Guzman" wrote:

    > > 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: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"

    Relevant Pages

    • Re: Execute Persmission denied on object sp_OACreate
      ... If so what access and permissions. ... The account is a windows account. ... One method to test permissions is to log in to your SQL Server box using the ... >>> SA account password and gaining access to the database. ...
      (microsoft.public.sqlserver.security)
    • Re: Specifying a logon account for SQL Server 2005 services
      ... As I mentioned using a Windows domain account with no permissions is the ... configured only for very specific server and memory configurations. ... account to use while installing SQL Server, will SQL Server give this account ...
      (microsoft.public.sqlserver.security)
    • Re: simple security question (need refresher)
      ... If IIS is configured for anonymous access and you connect to SQL Server ... using Windows authentication, the IIS anonymous account ... this account or roles that this account is a member of. ... Permissions are required only on objects directly accessed by the ...
      (microsoft.public.sqlserver.security)
    • Re: "Access denied" with xp_cmdshell (!)
      ... But problem is that I have tried to give for that account full control ... restart Sql Server for those permissions to take effect. ...
      (microsoft.public.sqlserver.security)
    • Re: "Access denied" with xp_cmdshell (!)
      ... Does the command work fine if you execute it from a prompt started as the ... service account? ... Have you checked both the permissions for the share and for the file? ... restart Sql Server for those permissions to take effect. ...
      (microsoft.public.sqlserver.security)