Impersonation for sp_OA* on SQL Server 2000

Question

Is there any way to impersonate for sp_OA* calls? I'm specifically using this for file system management, and I'm doing everything in TSQL to avoid DTS since there's a possibility of upgrading to 2005 and I want this solution to be cross-version. Unfortunately, I need to access remote file systems but I can't get AD permissions added to the system account for one of the servers (it's under the radar and not on AD, though it runs a production ETL cycle). Is there any way to impersonate for this? I'd take an xp_cmdshell solution too; I just want it to be possible at all to avoid developing for both DTS and SSIS and juggling two code bases.

Again, this is not impersonation for execution of SQL Server objects; this is for accessing non-SQL Server resources using an account other than the SQL Server system account. The only solutions I've been able to find involve SQLCLR on 2005+, not that I would be able to deploy assemblies anyways. (I'm a consultant outside IT and there's political stuff.)

Answer 1

No, sp_OA runs in the context of the service account.

`xp_cmdshell' has a proxy option (different in SQL 2000) though which may work.

In SQL Server 2005, it's a lot more locked down (like sp_OA%) by default, so this may not suitable politically. We still have some legacy DTS packages running against SQL 2005 (using dtsrun, from filesystem) so it is still available and has an SSM plug in to manage them.