Re: Security level to run SP_OA... Procedures
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 08/07/03
- Next message: Kostas Mastrogiannis: "SQL 2000 Security Problem"
- Previous message: J: "Phantom User or Role"
- In reply to: Ric Pullen: "Security level to run SP_OA... Procedures"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 7 Aug 2003 08:22:44 -0500
I'm not sure why you are executing sp_OA* procs here since you can
access COM objects directly from DTS using VBScript. The only rationale
I can think of is that your DTS package runs on the client and you need
to invoke the object on the server for some reason.
Cross-database ownership chaining allows you to lock down access to the
sp_OA* procs as well as other master database stored procedures. You
can implement this as follows and should do so only if you fully trust
users with rights to create dbo-owned objects.
1) Encapsulate your T-SQL code in a stored procedure. The proc must be
owned by 'dbo' and the user database owned by 'sa'. You can change db
ownership with sp_changedbowner, if necessary.
2) If you are using SQL 2000 SP3, enable cross-database chaining in the
user database.
3) Configure the SQL Agent proxy account. You can do this from
Enterprise Manager under Management --> SQL Agent properties --> Job
System. Uncheck the 'Only users with sysadmin privileges...' checkbox.
4) No sp_OA* permissions are needed. Users need only execute
permission on your user procedure.
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Ric Pullen" <Richard.Pullen-REMOVESPAM@Hospital.nhs.uk> wrote in message news:unMtNJNXDHA.1900@TK2MSFTNGP10.phx.gbl... > I have created a DTSpackageExecute (found on the web) that uses the > sp_OACreate, spOA_Method, spOA_getproperty and spOA_Destroy. > > My application uses SQL authentication, and access levels are determined > within the application on who can run what. > > This sp executes a package for a couple of users only to extract data in a > CSV format for manual uploading to another system. (has to be this way). > > Can anyone tell me where abouts do i need to set the security to run this > sp. > > What is the minimum level of access do i have to create to run the sp_OA... > procedures. > > Thanks for your help. > > >
- Next message: Kostas Mastrogiannis: "SQL 2000 Security Problem"
- Previous message: J: "Phantom User or Role"
- In reply to: Ric Pullen: "Security level to run SP_OA... Procedures"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
