Re: Execute Persmission denied on object 'sp_OACreate'
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 02/15/05
- Previous message: Sue Hoegemeier: "Re: using logins"
- In reply to: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 14 Feb 2005 19:46:54 -0600
> It is strange though -- in my dev server I did not have to make the owner
> be
> 'SA' but simply gave the existing owner the 'SA' role.
Yes, this is strange. I wouldn't expect database owner server role
membership to make any difference in cross-database chaining behavior.
Perhaps the user's login is a sysadmin role member on the dev server?
-- Hope this helps. Dan Guzman SQL Server MVP "WhiskyRomeo" <WhiskyRomeo@discussions.microsoft.com> wrote in message news:2D6C4968-5709-4416-8D63-80E556C15557@microsoft.com... > Ok, the last two suggestions made it work -- I missed them earlier -- > sorry > about that. > > It is strange though -- in my dev server I did not have to make the owner > be > 'SA' but simply gave the existing owner the 'SA' role. However, that did > not > work in the Production server. The owner had to be the 'SA'. Both > environment are Windows 2003. > > Bill > > "Dan Guzman" wrote: > >> > 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 >> >> >> >> > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>
- Previous message: Sue Hoegemeier: "Re: using logins"
- In reply to: WhiskyRomeo: "Re: Execute Persmission denied on object 'sp_OACreate'"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
