Re: Execute Persmission denied on object 'sp_OACreate'
From: WhiskyRomeo (WhiskyRomeo_at_discussions.microsoft.com)
Date: 02/11/05
- Next message: Adam Machanic: "Re: DBA Role"
- Previous message: James Lennin: "DBA Role"
- In reply to: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Next in thread: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Reply: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 11 Feb 2005 12:27:04 -0800
Dan,
This did not work. I created a user called SqlAgent. I set this as the
proxy account to use in the Job Systems tab of SQL Server Agent Properties.
I logged in as SqlAgent was able execute the DTS via the client application.
Then created a user and made him a member of a group which has the
appropriate permission. I logged in as that user, tried to execute the DTS
via the client application and the same access denied errors as stated
previously.
What I am missing? What relation does this proxy account have to other
users? Does Sql Server Agent recognize that a user does not have SysAdmin
priviliges and automatically invoke the proxy account to execute these
sp_OAxxxx stored procedures?
Bill
"Dan Guzman" wrote:
> > 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: Adam Machanic: "Re: DBA Role"
- Previous message: James Lennin: "DBA Role"
- In reply to: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Next in thread: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Reply: Dan Guzman: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
