Re: Execute Persmission denied on object 'sp_OACreate'
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 02/12/05
- Next message: Derek: "Re: permissions gone missing in sysprotects"
- Previous message: gordon: "Log shpping security/access problem...."
- In reply to: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Next in thread: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Reply: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Fri, 11 Feb 2005 21:45:30 -0600
> I logged in as that user, tried to execute the DTS
> via the client application and the same access denied errors as stated
> previously.
Just to be clear, I understand this user is executing a stored procedure
which then launches the DTS package using the sp_OA* procs? In that case,
ensure your user database is owned by 'sa' (sp_helpdb) and that the 'db
chaining' option is enabled (sp_dboption)..
> 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?
Yes, when a non-sysadmin role member executes an sp_OAxxxx proc, the proxy
account is used during proc execution.
-- Hope this helps. Dan Guzman SQL Server MVP "WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message news:F00C361E-D230-4C56-9D06-83A02B8B11D4@microsoft.com... > 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: Derek: "Re: permissions gone missing in sysprotects"
- Previous message: gordon: "Log shpping security/access problem...."
- In reply to: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Next in thread: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Reply: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
