DTS Package to Change Object Owner for Excel Impor

Question

Post reply

DTS Package to Change Object Owner for Excel Impor

CherylDixon

Old Hand

Points: 333
More actions
April 22, 2003 at 8:05 am

#106389

Hi:

I have a development project where I'm using an Microsoft Access (ADP) Project file front-end and SQL Server 2000 backend. Users are logged on via the Windows NT authentication. I import a file into SQL Server via a function within a module using as part of the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97...

I need for the imported spreadsheet that becomes a table in SQL Server to automatically set the object owner as 'dbo' instead of the current username (who is performing the import).

Can I use a DTS package to get this done? If so, please explain how.

Thanks,

Cheryl
April 25, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

You could accomplish the owner change through DTS, a job, ADO command from your app, etc.

The stored proc you will want to execute is sp_changeobjectowner. Look in SQL Server Books Online for examples.

The one constraint is with the permissions to execute this procedure. Books Online outlines as follows;

"Only members of sysadmin fixed server role, the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner."

So, a DTS package with a job firing under the permissions of SQL Server Agent Service may be your best solution.

Hope this helps and post again if you need further details.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

CherylDixon Old Hand Points: 333 More actions · Answer 2

Hi DavidB:

Thanks for your response. I am familar with the built-in stored proc called 'sp_changeobjectowner'.

I use it all the time, especially to change owner to dbo. For example, I often use something like the following in Query Analyzer:

exec sp_changeobjectowner 'dixonca.Table1', 'dbo'

Can I schedule a DTS package to eun based on a click event (button) on a form? Is so, how can I do it?

How can I accomplish the task via a ADO command in my application?

Please provide further details. Thanks again for the help.

Cheryl Dixon

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

You can use a command line to run a DTS package. The best way that I have found to run a DTS package on an as needed basis is to create a job to run it (i.e. right click on the package, schedule) and then leave the job disabled so that the job does not run by itself.

Once you have that you can use the sp_start_job to run the job / package. I like this option better because you can then see the job name in the code that you are running rather than using the DTS run command (DTSRun /~S 0x18CF91F7708E09CACB27369D2FE049EC42FFBAEF8638C22E /~N 0x953DECD59905CCB02A4C1CD359F1D4C944771396F05B5F9C995BB911520F93E5E41D0D3B5F456E78F54BAC65F60AF4DC5CB698DC5591A553 /E).

I realize you could comment your code but, isn’t sp_start_job ‘DBName_ChangeTable1OwnerToDBO’ nicer?

Just my thoughts and hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot