Re: Execute Persmission denied on object 'sp_OACreate'

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

  • Next message: Agent_C: "Re: Re: Re: I have been asked to leave the company for having spotted serious security breaches" 
    Date: Sat, 5 Feb 2005 11:47:57 -0600

    > 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 account is a windows account. It will need permissions to whatever your
    DTS package requires. For example, if your package creates a file, the
    account will need permissions to the target folder or share. Similarly, if
    the packages establishes a trusted SQL connection, the account will need
    granted access to SQL Server and access to needed databases just like any
    user.

    One method to test permissions is to log in to your SQL Server box using the
    proxy account and then execute the DTS package interactively. 

    -- 
Hope this helps.
Dan Guzman
SQL Server MVP
"WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message 
news:2BCA3B22-55DE-45A0-9586-5CCFDFBCBEA3@microsoft.com...
> 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: Agent_C: "Re: Re: Re: I have been asked to leave the company for having spotted serious security breaches"

    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: Create basic user for one database
      ... The 'Admin' account and 'Users' group are common to every installation ... assign the correct permissions to the Users group, ... database without supplying a password. ...
      (microsoft.public.access.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: 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)