Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 DTS package to SQL permissions...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

14 Posts

Posted - 03/15/2013 :  13:29:32  Show Profile  Reply with Quote

I'm running a DTS package that creates a CSV file by using 2 temp tables that are created and then dropped.

I run the DTS from a batch file, that runs the DTSRUN utility on a users workstation.

It errs on creating the temp files unless I set the current SQL LOGIN user permissions to "SYSTEMS ADMINISTRATOR" under sever roles, which I DO NOT WANT for these workstation users.

I looked at set permissions like create, set, update etc under DATABASE ACCESS, however I can't chose a TEMP database for access that does not exist or is created and then dropped!

Also, with certain permissions, the DTS will create the temp table, but then it will assign the workstation user as the OWNER and then the DTS will not run because of that.

Note these temp tables and not GLOBAL temp tables (not stored in tempDB) they are created and dropped within my SQL db.

Any ideas here on where and what to set? I just can't figure it out.

Thanks much,


Edited by - mrpush on 03/15/2013 13:32:39


5072 Posts

Posted - 03/16/2013 :  13:56:44  Show Profile  Visit russell's Homepage  Reply with Quote
Anyone can create temp tables. No special permissions are required. And all temp tables are created in tempdb.

See here and scroll down to Temporary Tables

Might also look at this article by Graz from back in the day when we were all still using SQL 2000

Edited by - russell on 03/16/2013 13:59:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000